Hiding unused rows

inarbeth

Well-known Member
Joined
Apr 8, 2002
Messages
913
I want automatically to hide rows that contain no data just before printing.
A cell in the row to be hidden in Sheet 2 contains the formula: =IF(Sheet1!A15=0,"",Sheet1!A15) so we need to look at the result and not that the cell contains nothing. Users will enter figures on Sheet 1 which are then formatted and totalled on Sheet 2.
If cells A15 and A16 on Sheet 1 are left blank, I want the corresponding rows (not necessarily rows 15 and 16) in Sheet 2 to reduce to zero width.
I think I need a macro with a series of If Then operations but have been struggling with my very basic knowledge of VBA.
Users might need to go back later and put information in A15 or A16 or both so the macro would have to unhide (and preferably autofit the relevant rows in Sheet 2) when run again. I would also like to restore normal row heights to the rows on Sheet 2 on exit from the worksheet.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi
I don't know what the corresponding rows are?
I would put a simple macro in the Workbook_BeforePrint event or Worksheet_Change on sheet1 which would hide the rows. Then restore the rows in the Worksheet_Change on sheet1, testing for an added value in A1.
How do you know which rows to hide???
Maybe you could change your formula to:
=IF(Sheet1!A15=0,"HideRow",Sheet1!A15)
Which columns contains these formulas?
Tom
 
Upvote 0
The corresponding rows are the ones left blank on Sheet 2 if A15 and A16 are empty but which will contain the contents of cell A15 and cell A16 on sheet 1 otherwise. The reference to corresponding rows was so that the macro can select which rows to hide. If someone can kindly give me the basic code, I should be able to edit it to hide the relevant rows on Sheet 2.
 
Upvote 0
'To hide rows 6 & 7
Rows("6:7").EntireRow.Hidden = True
'To hide row 6 only
Rows("6:6").EntireRow.Hidden = True

Change the true to false to unhide
 
Upvote 0
Thank you Tom. I may not be making myself clear. I need the If Then bit as well and presumably an If Then to unhide on a repeat pass if figures are later put in cells A15 and A16.
 
Upvote 0
No you are not making yourself clear...
If then what?
There are 256 cells in one row.
Which column(s) intersecting the rows in question holds the determining value.
If A5, B5, C5 = "" then hiderow?
How many rows deep? 65536?
Please give more detail...
Thanks,
Tom
 
Upvote 0
Sorry,
For the sake of the example, Cell B18 on Sheet 2 will contain the formula =Sheet1!A15. Cell B20 on Sheet 2 will contain the formula =Sheet1!A16. If cell A15 on Sheet 1 is empty then hide Row 18 on Sheet 2. If cell A16 on Sheet 1 is empty then hide Row 20 on Sheet 2.

The user might go back to Sheet 1 from Sheet 2 and add data to A15 and/or A16. If the user is undecided he or she might go back again and clear the cells again.

So on re-running the macro, if cell A15 on Sheet 1 is not empty then autosize Row 18 on Sheet 2. If cell A16 on Sheet 1 is not empty then autosize Row 20 on Sheet 2.

I will attach the macro to a button taking the user from Sheet 1 to Sheet 2. Therefore the macro will run each time and needs to test whether cells A15 or A16 are empty or not. The user will see that rows 18 and 20 on Sheet 2 will be hidden or unhidden depending on whether anything has been put in A15 and A16 on Sheet 1.
 
Upvote 0
Gotcha!
I hope

Sub RowStuff()
Dim RowCntr As Long
For RowCntr = 1 To 1000 'or increase number of rows if necc.
If Sheet2.Range("B" & RowCntr).Value = "" Then
'if the value of B? = "" then hide row
Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.Hidden = True
Else
'if the value of B? <> "" then un-hide & autofit row
Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.Hidden = False
Sheet2.Rows(RowCntr & ":" & RowCntr).EntireRow.AutoFit
'if you would rather set to a predetermined row height use:
'Sheet2.Rows(RowCntr & ":" & RowCntr).RowHeight = 7.5
End If
Next
End Sub

Tom
 
Upvote 0
Thank you for all the time you are taking but we are not quite there yet. I don't want to hide all rows except those corresponding to rows in Sheet 1 with stuff in them. I want to hide only the single rows concerned. Thus if there is nothing in A15 in Sheet 1, row 18 in Sheet 2 (but no others) will be hidden. I gave the example of two rows because I might wish to make it 3 or 4 and the code may be different from that for just a single row.
I am hiding the rows so that when Sheet 2 is printed I avoid what would otherwise be acres of blank space. Thanks
 
Upvote 0
Playing around with your code (and with a bit of help from the macro recorder) I have come up with the following code:

Sub RowStuff()
If Sheet1.Range("A15").Value = "" Then
Sheet2.Rows("18:18").EntireRow.Hidden = True
Else
Sheet2.Rows("18:18").EntireRow.Hidden = False
Sheet2.Rows("18:18").EntireRow.AutoFit

End If

End Sub

How do I get it to repeat to hide other rows, eg if A16 is empty to hide Row 20 on sheet 2? I expect the answer is very simple.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top