Conditional hiding of cells when printing (2003)

Topic

New Reply

I want to have rows in a worksheet not print if the value in a specific cell in the row is zero or blank. Manually hiding and unhiding the row is a pain, and formatting the row as hidden does not work as it displays and prints a blank row. I want to be able to see the row on the screen, but not have it print and not print a blank row. Just like hidden text in Word. Is there any way to do this?

Reply | Quote

Replies

You could create a macro that:
1) hides rows if a specific cell in that row is zero or blank
2) prints the worksheet
3) unhides the rows.
Assign this macro to a keyboard shortcut and/or custom toolbar button. Use this to print the sheet.
If you need help writing the macro, please provide more details.

Reply | Quote

I had wondered about something along those lines, but I’m not a macro writer. What I want is to test all the cells in a column in a range (I want to exclude headers and totals from the test) and if a particular cell is blank or zero, then the row that contains that cell will not print. There could be no, one or several rows that meet the test.

Reply | Quote

That is not very specific, so you won’t get a specific answer. The following code assumes that the cell to be tested is in column A, and that you want to test rows 4 through 18.

Sub cmdPrint_Click()
Dim i As Long
‘ Loop through cells, hide row if necessary
For i = 4 To 18
If Range(“A” & i) = 0 or Range(“A” & i) = “” Then
Range(“A” & i).EntireRow.Hidden = True
End If
Next i
‘ Print
ActiveSheet.PrintOut
‘ Loop through cells to unhide rows
For i = 4 To 18
Range(“A” & i).EntireRow.Hidden = False
Next i
End Sub

Reply | Quote

OK, thanks for that. Rather than naming specific rows or columns, I want to use a named range. So in your example instead of testing column A rows 4 through 18, I would test all the cells in a range named ‘Quantity’. How would that be incorporated into the macro?

Reply | Quote

Try this variation:

Sub cmdPrint_Click()
Dim oCell As Range
‘ Loop through cells, hide row if necessary
For Each oCell In ActiveSheet.Range(“Quantity”)
If oCell = 0 or oCell = “” Then
oCell.EntireRow.Hidden = True
End If
Next oCell
‘ Print
ActiveSheet.PrintOut
‘ Loop through cells to unhide rows
For Each oCell In ActiveSheet.Range(“Quantity”)
oCell.EntireRow.Hidden = False
Next oCell
Set oCell = Nothing
End Sub

Reply | Quote

Thanks, that worked like a charm. I assigned a toolbar button to the macro and of course it appears in every instance of Excel, regardless of the workbook that’s open. Is there a way to have the button appear only when the workbook containing the macro is open?

Reply | Quote

You can create the command button in code when the workbook is opened, and delete is when the workbook is closed. But if you want to do it really professionally, it’s a lot of work – you would also have to hide the button when the user switches to another workbook and make it visible again when the user switches back. You can also put the toolbar button on a custom toolbar, attach the toolbar to the workbook and delete it each time you close the workbook. See for example post 321692.

Reply | Quote

You can create the command button in code when the workbook is opened, and delete is when the workbook is closed. But if you want to do it really professionally, it’s a lot of work – you would also have to hide the button when the user switches to another workbook and make it visible again when the user switches back. You can also put the toolbar button on a custom toolbar, attach the toolbar to the workbook and delete it each time you close the workbook. See for example post 321692.

Reply | Quote

Thanks, that worked like a charm. I assigned a toolbar button to the macro and of course it appears in every instance of Excel, regardless of the workbook that’s open. Is there a way to have the button appear only when the workbook containing the macro is open?

Reply | Quote

Try this variation:

Sub cmdPrint_Click()
Dim oCell As Range
‘ Loop through cells, hide row if necessary
For Each oCell In ActiveSheet.Range(“Quantity”)
If oCell = 0 or oCell = “” Then
oCell.EntireRow.Hidden = True
End If
Next oCell
‘ Print
ActiveSheet.PrintOut
‘ Loop through cells to unhide rows
For Each oCell In ActiveSheet.Range(“Quantity”)
oCell.EntireRow.Hidden = False
Next oCell
Set oCell = Nothing
End Sub

Reply | Quote

OK, thanks for that. Rather than naming specific rows or columns, I want to use a named range. So in your example instead of testing column A rows 4 through 18, I would test all the cells in a range named ‘Quantity’. How would that be incorporated into the macro?

Reply | Quote

That is not very specific, so you won’t get a specific answer. The following code assumes that the cell to be tested is in column A, and that you want to test rows 4 through 18.

Sub cmdPrint_Click()
Dim i As Long
‘ Loop through cells, hide row if necessary
For i = 4 To 18
If Range(“A” & i) = 0 or Range(“A” & i) = “” Then
Range(“A” & i).EntireRow.Hidden = True
End If
Next i
‘ Print
ActiveSheet.PrintOut
‘ Loop through cells to unhide rows
For i = 4 To 18
Range(“A” & i).EntireRow.Hidden = False
Next i
End Sub

Reply | Quote

I had wondered about something along those lines, but I’m not a macro writer. What I want is to test all the cells in a column in a range (I want to exclude headers and totals from the test) and if a particular cell is blank or zero, then the row that contains that cell will not print. There could be no, one or several rows that meet the test.

Reply | Quote

You could create a macro that:
1) hides rows if a specific cell in that row is zero or blank
2) prints the worksheet
3) unhides the rows.
Assign this macro to a keyboard shortcut and/or custom toolbar button. Use this to print the sheet.
If you need help writing the macro, please provide more details.

Reply | Quote