Hide & Seek (Rows that is)

[Bob]

Hi all,
Back again with another Q. Is it possible to create a macro or
something so that when you print, it will autmoatically hide rows within a
certain rage when a column's value = 0 (the number zero not null)

Example.
A price list with 5 rows (rows 13 - 17). Column A is the "Quantity" value.
We're only ordering two things from this list (on row 14 & 16). So I want
to hide all the other rows within the 13 - 17 range where Column A quantity
vaule is equal to 0 (instead of quantity 1 or 2, etc) so that it only prints
what we're buying.

Thanks again for the help.
Bob

 

[Ron de Bruin]

Hi Bob

Try this for a sheet named Sheet1

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Sheet1")
For rw = 13 To 17
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A13:A17").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

 

[Bob]

That seemed to work well, but is there a way to make this happen whenever
you print or print preview the document. It seems like I have to manually
run the macro before I print in order for it to format properly.

Thanks again,
-Bob

 

[Ron de Bruin]

Hi Bob

You can run code like this automatic in the beforeprint event

Copy/Paste this event in the Thisworkbook module

1) Copy the code
2) Right click on the Excel icon next to File in the menu bar
3) Choose view code
4) Paste the code
5) Alt-Q to go back to Excel


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rw As Long
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
For rw = 13 To 17
If .Cells(rw, 1).Value = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintOut ' for testing use .PrintPreview
.Range("A13:A17").EntireRow.Hidden = False
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub