Macro to Hide blank rows

[manfareed]

Hi ,

I have the following code wich hides lines in an income statement if active
cell is equal to "Hide". "Hide" is displayed when a divisional total =0.
Each division is shown on a separate page.

Sub Hide_Blanks()

Range("AU14").Select

' Test contents of active cell; if active cell is "end"
Do Until ActiveCell = "end"

ActiveCell.Select
If ActiveCell = "Hide" Then Call hideblanks

' Step down 1 row to the next cell.
ActiveCell.Offset(1, 0).Select

' Return to top of loop.
Loop

Range("a8").Select

End Sub
Sub hideblanks()

ActiveCell.Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PageBreak = False
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.EntireRow.Hidden = True
Selection.End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
End Sub


For some reason it hides the rows with data but when I come tp print the
spreadsheet the page still prints off. Tha page is not completely hidden.

I hope I am making sense.

Thanks,

 

[Mike H.]

I am not sure I was following your example. But if your printing is
automated too, perhaps you should share that code because it sounds to me
like you're printing basically a blank page and you wish to skip that page,
right? If that is the case, then your printing code should evalute to see if
there are non-hidden lines and if not, then skip the print routine. HTH

 

[manfareed]

Hi,

Thanks for your reply. You are right about it printing a blank page. But
there is no print macro. Each division is split via a horizontal page break.

Thanks,

 

[Mike H.]

I am not sure what you'd have to do, but if it were me, I'd create a print
macro and then create different Custom Views (See help for how to do this).
Then in the macro evaluate each division to see if there is unhidden lines or
not. If so, switch to that division's custom view and then print the report.
Otherwise, you'd just skip it. Here is a sample:

Sub PrintOutReports
Dim Div1Visible as double
Dim Div2Visible as double
'You'll need code here to set values to Div?Visible
If Div1Visible>0 then
ActiveWorkbook.CustomViews("Division1").Show
ActiveWindow.SelectedSheets.PrintPreview
end if
if Div2Visible>0 then
ActiveWorkbook.CustomViews("Division2").Show
ActiveWindow.SelectedSheets.PrintPreview
end if

End sub

You can change the printpreview to print outs as needed. HTH

 

[manfareed]

Hi Mike,

Thanks for your help. I'll follow your advice.

Regards,

Manir