How can we hide the complete record in excel

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

How can we hide the complete record in excel

Post by 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
You do not have the required permissions to view the files attached to this post.
Last edited by prince on 15 Jun 2016, 03:28, edited 1 time in total.

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: How can we hide the complete record in excel

Post by 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
You do not have the required permissions to view the files attached to this post.
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78790
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How can we hide the complete record in excel

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Re: How can we hide the complete record in excel

Post by 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

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: How can we hide the complete record in excel

Post by Rudi »

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

If your absence does not affect them, your presence didn't matter.

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Re: How can we hide the complete record in excel

Post by 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

User avatar
HansV
Administrator
Posts: 78790
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How can we hide the complete record in excel

Post by HansV »

The line

Code: Select all

    Set wsh2 = Sheet2
specifies the sheet on which rows are hidden. You can change this line.
Best wishes,
Hans

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: How can we hide the complete record in excel

Post by Rudi »

prince wrote: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
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Re: How can we hide the complete record in excel

Post by 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
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78790
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: How can we hide the complete record in excel

Post by 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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

prince
2StarLounger
Posts: 171
Joined: 02 Mar 2015, 17:00

Re: How can we hide the complete record in excel

Post by prince »

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