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
How can we hide the complete record in excel
-
- 2StarLounger
- Posts: 172
- Joined: 02 Mar 2015, 17:00
How can we hide the complete record in excel
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.
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can we hide the complete record in excel
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.
Please check that the macro runs accurately for you.
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.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- Administrator
- Posts: 78868
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can we hide the complete record in excel
And here is an alternative:
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.
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
The macro can be run by clicking the button on Sheet 1.
Remark: I corrected "inforamtion" to "information" on Sheet2.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 172
- Joined: 02 Mar 2015, 17:00
Re: How can we hide the complete record in excel
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
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
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can we hide the complete record in excel
Hmmm....it is working 100% for me on the sample you sent.
Anyways, its good that Han's code works for you.![Cheers :cheers:](./images/smilies/cheers.gif)
Anyways, its good that Han's code works for you.
![Cheers :cheers:](./images/smilies/cheers.gif)
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 172
- Joined: 02 Mar 2015, 17:00
Re: How can we hide the complete record in excel
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
Regards,
Prince
-
- Administrator
- Posts: 78868
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can we hide the complete record in excel
The line
specifies the sheet on which rows are hidden. You can change this line.
Code: Select all
Set wsh2 = Sheet2
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: How can we hide the complete record in excel
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. Cheersprince 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 .
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- 2StarLounger
- Posts: 172
- Joined: 02 Mar 2015, 17:00
Re: How can we hide the complete record in excel
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
Thanks & Regard
Prince
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78868
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: How can we hide the complete record in excel
Right-click a signature and select Size and Properties from the context menu.
Select Properties in the navigation pane on the left.
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.
Select Properties in the navigation pane on the left.
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
Hans
-
- 2StarLounger
- Posts: 172
- Joined: 02 Mar 2015, 17:00
Re: How can we hide the complete record in excel
Thanks Mr. Hans , Its working so cool .
Thanks a lot.
Regards,
Prince
Thanks a lot.
Regards,
Prince