Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a macro to set the print range only for visible rows
I have a print range of say 1:10, and hide row 6.
It looks correct. You see 1-5,7-10. If I have a range of pages (say 15 pages) and hide all of the rows that end up on page 6, it prints a blank page 6 with headers, etc. Basically, I need a macro that goes through a workbook, logs what rows are hidden, and then takes them out of the final print range and sets the print range only for visible rows. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
need a macro to set the print range only for visible rows
I bet you're running xl2k (or below).
I think what's happening is that even though you hid some rows, excel's print engine still finds page breaks that are in those hidden rows. And even worse, it respects those page break marks and forces new pages when you print. And to make matters worse (well, maybe), if you use a multi-area print range, then each area will be printed on its own sheet of paper. If that's not a problem, you may be able to use a routine like this: Option Explicit Sub testme() Dim wks As Worksheet Dim ExistingPrintRng As Range Dim VisiblePrintRng As Range Set wks = Worksheets("Sheet1") With wks 'make sure that the printarea has been set! Set ExistingPrintRng = Nothing On Error Resume Next Set ExistingPrintRng = .Range(.PageSetup.PrintArea) On Error GoTo 0 'if the print range hasn't been set, use the .usedrange If ExistingPrintRng Is Nothing Then Set ExistingPrintRng = .UsedRange End If Set VisiblePrintRng = Nothing On Error Resume Next Set VisiblePrintRng _ = ExistingPrintRng.Cells.SpecialCells(xlCellTypeVisi ble) On Error GoTo 0 If VisiblePrintRng Is Nothing Then MsgBox "Nothing to print!" Else VisiblePrintRng.PrintOut preview:=True 'preview for testing End If End With End Sub Essentially, it just selects the print range, then uses Edit|goto|special|visible cells only and then prints that. ========== If it's a problem that each area of that multi-area print range prints on its own sheet, you could copy the worksheet (or data as values and formats and columnwidths and rowheights and ...) to a new worksheet, delete the hidden rows and print normally. I _think_ xl2002 was the first version of excel to ignore the page breaks on those hidden rows. So upgrading may be another choice???? On 07/12/2010 06:19, vicky wrote: I have a print range of say 1:10, and hide row 6. It looks correct. You see 1-5,7-10. If I have a range of pages (say 15 pages) and hide all of the rows that end up on page 6, it prints a blank page 6 with headers, etc. Basically, I need a macro that goes through a workbook, logs what rows are hidden, and then takes them out of the final print range and sets the print range only for visible rows. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print Visible Sheets Macro | Excel Discussion (Misc queries) | |||
Macro Print Range Problem - Rows Cut Off (Upgraded From Excel 2003 to2007) | Excel Programming | |||
Print Only Visible Rows Using AutoFilter | Excel Programming | |||
Print visible rows only | Excel Programming | |||
Need Help w/ Print Macro to Print All Visible Sheets (including Charts) in a Workbook | Excel Programming |