Hiding unused rows

inarbeth · Apr 9th, 2002, 02:54 PM

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.

Tom Schreiner · Apr 9th, 2002, 03:04 PM

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

inarbeth · Apr 10th, 2002, 06:10 AM

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.

Tom Schreiner · Apr 10th, 2002, 06:28 AM

'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

inarbeth · Apr 10th, 2002, 06:33 AM

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.

Tom Schreiner · Apr 10th, 2002, 06:42 AM

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

inarbeth · Apr 10th, 2002, 07:03 AM

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.

Tom Schreiner · Apr 10th, 2002, 10:25 AM

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

inarbeth · Apr 10th, 2002, 03:18 PM

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

inarbeth · Apr 13th, 2002, 02:46 AM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 9th, 2002, 02:54 PM	#1
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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.

Apr 9th, 2002, 03:04 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 10th, 2002, 06:10 AM	#3
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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.

Apr 10th, 2002, 06:28 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	'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

Apr 10th, 2002, 06:33 AM	#5
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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.

Apr 10th, 2002, 06:42 AM	#6
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 10th, 2002, 07:03 AM	#7
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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.

Apr 10th, 2002, 10:25 AM	#8
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	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

Apr 10th, 2002, 03:18 PM	#9
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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

Apr 13th, 2002, 02:46 AM	#10
inarbeth Board Regular Join Date: Apr 2002 Location: Wivenhoe, England Posts: 877	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.