Macro to Hide blank rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

manfareed - 19 May 2008 17:21 GMT

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,

Reply to this Message

Mike H. - 20 May 2008 14:59 GMT

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

> Hi ,
>
[quoted text clipped - 39 lines]
>
> Thanks,

Reply to this Message

manfareed - 20 May 2008 17:19 GMT

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,

> 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
[quoted text clipped - 45 lines]
> >
> > Thanks,

Reply to this Message

Mike H. - 20 May 2008 17:48 GMT

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

> Hi,
>
[quoted text clipped - 52 lines]
> > >
> > > Thanks,

Reply to this Message

manfareed - 21 May 2008 10:00 GMT

Hi Mike,

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

Regards,

Manir

> 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).
[quoted text clipped - 75 lines]
> > > >
> > > > Thanks,

Reply to this Message