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

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+V 592+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

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

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 Guillett
Microsoft MVP Excel
SalesAid Software

"manfareed" wrote in message
...
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+V 592+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

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

Don,

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

Regards,

Manir

"Don Guillett" wrote:

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 Guillett
Microsoft MVP Excel
SalesAid Software

"manfareed" wrote in message
...
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+V 592+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

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

Hi,

I have just emailed the wb to your private address.

Thanks,

Manir

"Don Guillett" wrote:

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 Guillett
Microsoft MVP Excel
SalesAid Software

"manfareed" wrote in message
...
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+V 592+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