Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Hide blank rows
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Hide blank rows
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" wrote: 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Hide blank rows
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." wrote: 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" wrote: 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, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Hide blank rows
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 Div1Visible0 then ActiveWorkbook.CustomViews("Division1").Show ActiveWindow.SelectedSheets.PrintPreview end if if Div2Visible0 then ActiveWorkbook.CustomViews("Division2").Show ActiveWindow.SelectedSheets.PrintPreview end if End sub You can change the printpreview to print outs as needed. HTH "manfareed" wrote: 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." wrote: 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" wrote: 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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Hide blank rows
Hi Mike,
Thanks for your help. I'll follow your advice. Regards, Manir "Mike H." wrote: 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 Div1Visible0 then ActiveWorkbook.CustomViews("Division1").Show ActiveWindow.SelectedSheets.PrintPreview end if if Div2Visible0 then ActiveWorkbook.CustomViews("Division2").Show ActiveWindow.SelectedSheets.PrintPreview end if End sub You can change the printpreview to print outs as needed. HTH "manfareed" wrote: 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." wrote: 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" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to HIDE blank rows | Excel Worksheet Functions | |||
How to hide rows, but not blank rows used for formatting | Excel Programming | |||
Macro hide rows with blank data in cells | Excel Programming | |||
Macro to hide blank rows | Excel Programming | |||
macro to hide rows if cell is blank | Excel Worksheet Functions |