Hide blank rows - what is wrong with this code ???

  • Thread starter Thread starter manfareed
  • Start date Start date
M

manfareed

Hi ,

I have a divisional income statement which has the following formula to hide
any rows relating to a particular division if the balance is "zero".

It worked ok until I extended the print area at the bottom of the
spreadsheet. The code below ends well before that. It still hides rows but
prints the page with the hidden division. Previously it did not "print" a
page if the division was hidden. I do not want a page printed if the division
is hidden.



' Note that you can also use a Do While Not IsEmpty(ActiveCell) loop.
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

'Hide contains the following formula
=IF(SUM(V623+Z623+AD623+V619+Z619+AD619+V615+Z615+AD615+V604+Z604+AD604+V598+Z598+AD598+AD592+Z592+V592+V590+Z590+AD590+AD581+Z581+V581)=0,"Hide","No")
; cell refs differ for each division'

' 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

Any help would be appreciated.

Thanks
 
You probably need an addition to reset the print area.
Probably do NOT need the page break line. Excel doesn't print hidden rows.

It looks like the whole thing is a bit clumsy. Post your layout or, if
desired, send your wb to my private address below along with detailed
instructions of your desires.
 
Don,

Thanks for your reply. I will email yourself a copy of the spreadsheet.

Regards,

Manir
 
Back
Top