| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Hide Blank Rows | Excel Worksheet Functions | |||
| VBA code to hide blank rows | Excel Worksheet Functions | |||
| How to hide rows, but not blank rows used for formatting | Excel Programming | |||
| Slow Running Code to Hide Blank Rows | Excel Programming | |||
| hide blank rows | Excel Programming | |||
Hybrid Mode
