need a macro to set the print range only for visible rows

[vicky]

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]

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(xlCellTypeVisible)
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????