How to print only rows with non zero values

[swhitesides]

I have a spreadsheet that is setup as a master template to accommodate 105 rows of data input. How can I make it display and print only the rows that non zero values have been entered. The final print needs to contain rows A1:A12 and A118:A129 along with A13:A117 containing non zero values only.

Data input is frequently changing so hiding the cells is not an acceptable option.

I was thinking a type of macro would solve this issue but all the ones I have tried are not working correctly.

 

[TheNoocH]

try something like this...

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 1
   'finds last used row in column A
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "A") = 0 Then Rows(i).Hidden = True
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.PrintOut
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

 

[jim may]

Seems like a new helper-column entitled "Prt?"
Manually Put "X" in rows 1:12 and 118:129
then an If formula in rows 13:117 testing
if TRUE then "X", otherwise ""

 

[swhitesides]

try something like this...

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 1
   'finds last used row in column A
   LastRow = Range("A" & Rows.Count).End(xlUp).Row
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "A") = 0 Then Rows(i).Hidden = True
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.PrintOut
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

Nooch,
This did not work. All rows printed.
I need for the rows A1:A12 to always print. (Kinda like a header)
I need for rows A118:A129 to alway print. (Kinda like a footer)
I need rows A13:A117 to print if there is data input (non zero value) in columns D, E,F, or G. If no data input (non zero value) then do not print these rows.

I have a screen shot that i would like to send but dont know how to paste it in this message.

Any help is much appreciated!

 

[sous2817]

Have a look at this thread:

http://www.mrexcel.com/forum/showthread.php?t=409877

 

[TheNoocH]

try this

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 13
   LastRow = 117
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "D") = 0 Or Cells(i, "E") = 0 Or Cells(i, "F") = 0 Or Cells(i, "G") = 0 Then Rows(i).Hidden = True
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.PrintOut
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

 

[TheNoocH]

Actually after re-reading your last post this is probably what you are after...

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 13
   LastRow = 18
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "D") <> 0 Or Cells(i, "E") <> 0 Or Cells(i, "F") <> 0 Or Cells(i, "G") <> 0 Then
       Else
          Rows(i).Hidden = True
       End If
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.PrintOut
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

 

[swhitesides]

Actually after re-reading your last post this is probably what you are after...

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 13
   LastRow = 18
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "D") <> 0 Or Cells(i, "E") <> 0 Or Cells(i, "F") <> 0 Or Cells(i, "G") <> 0 Then
       Else
          Rows(i).Hidden = True
       End If
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.PrintOut
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

Hi Nooch!,
This worked with one bit of tweaking. We changed the "or" to "and", which worked perfectly. Thanks for your help!

 

[swhitesides]

try this

Sub HideRowsFromBottom()
   Dim i As Long, LastRow As Long
   Application.ScreenUpdating = False
   StartRow = 13
   LastRow = 117
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
   'begin loop to check each row
   For i = LastRow To StartRow Step -1
       'if cell=0, hide row
       If Cells(i, "D") = 0 and Cells(i, "E") = 0 and Cells(i, "F") = 0 and Cells(i, "G") = 0 Then Rows(i).Hidden = True
   Next i
   Application.ScreenUpdating = True
   ActiveSheet.Display
   Rows(StartRow & ":" & LastRow).EntireRow.Hidden = False
End Sub

I tried to manipulate this macro so I could view the end result on screen rather than sending straight to printer. So I changed Active Sheet.Printout to ActiveSheet.Display. It works, however I get the following Run Time Error:
Run Time Error '438':
Object doesnt support this property or method.
Any suggestions on this issue and how to do I fix it?

 

[sous2817]

Try:

ActiveSheet.PrintPreview