How can we hide the complete record in excel

[prince]

Hello Sir, Have you any idea how can hide the record of sheet 2 ( group of 27 rows ) if the student is absent in sheet 1 record.
In this attached file if in sheet 1 we don't enter the obtained marks of student then i want that complete record in sheet 2 should be hidden. Is their any idea how can i solve this problem.
Withe Regards,
Prince

[Rudi]

Here is a macro I added to the workbook that should hide the appropriate set of 27 rows.
Please check that the macro runs accurately for you.

excel record.xlsm

[HansV]

And here is an alternative:

Code: Select all

Sub HideEmpty()
    Const FirstRow = 4 ' first data row on Sheet1
    Const MarksCol = 6 ' column F contains the marks
    Const NumRows = 27 ' number of rows per report
    Dim wsh1 As Worksheet
    Dim wsh2 As Worksheet
    Dim r As Long
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Set wsh1 = Sheet1 ' Sheet with list of students
    Set wsh2 = Sheet2 ' Sheet with reports
    LastRow = wsh1.Cells(wsh1.Rows.Count, 1).End(xlUp).Row
    wsh2.Cells.EntireRow.Hidden = False
    For r = FirstRow To LastRow
        If wsh1.Cells(r, MarksCol).Value = "" Then
            wsh2.Cells(NumRows * (r - FirstRow) + 1, 1).Resize(NumRows).EntireRow.Hidden = True
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

I have added some constants at the beginning to make it easier to change the layout of the sheets.
The macro can be run by clicking the button on Sheet 1.

Remark: I corrected "inforamtion" to "information" on Sheet2.

excel record.xlsm

[prince]

Mr. Rudi Your macro code is not working correctly . When i enter or remove the marks of any students it didnt show effect on second sheet.

Mr. Hans your code is working perfectly. Is this possibly if i want to apply this macro code to my other sheets. if it is possible, then Please tell me where i have needed to change this code.
With Regards,
Prince

[Rudi]

Hmmm....it is working 100% for me on the sample you sent.
Anyways, its good that Han's code works for you.

[prince]

Mr. Rudi , When i remove the obtained marks of all students, then the information is not hiding on sheet 2. Its showing as it is .
Regards,
Prince

[HansV]

The line

Code: Select all

    Set wsh2 = Sheet2

specifies the sheet on which rows are hidden. You can change this line.

[Rudi]

Mr. Rudi , When i remove the obtained marks of all students, then the information is not hiding on sheet 2. Its showing as it is .

There must be something different on your workbook as it still functions fine on my side whether there are marks in the Obtained column or not. No fuss though...you have a solution so this is irrelevant. Cheers

[prince]

Hello Sir, Your Record hiding code is working perfectly . Now the minor problem is during the Hiding of record in the signature column signature is not hiding and its showing on the first row of next record on sheet 2 kindly guide me how to hide the sign with complete record.
Thanks & Regard
Prince

[HansV]

Right-click a signature and select Size and Properties from the context menu.
Select Properties in the navigation pane on the left.

S235.png

You'll see that Object Positioning is set to "Move but don't size with cells".
Select "Move and size with cells" instead, then click OK.

Do this for all signatures. The code should then hide the signatures too.

[prince]

Thanks Mr. Hans , Its working so cool .
Thanks a lot.
Regards,
Prince