Hiding Page Breaks in Excel

Question

Following on from my previous questions:

I have implemented a series of checkboxes with which users can hide/unhide ranges of data. This is great - it works. The problem is when the users go to print - the hidden data is hidden but the pages are still there but blank.

If the page breaks are left to their own devices then everything is fine - there are no blank sections. When manual page breaks are used then you can see where the data was.

I've tried everything to get rid of the blank areas.

Copying the ranges and recalculating the page breaks is a no-go as the page breaks take upwards of 2 minutes for a fairly small report.

So, how can I print only the ranges that aren't hidden?

Thanks, G.

Answer 1

Haven't tested it, but you could change the print range to only the visible cells:

Public Sub SetPrintRangeToVisible(ByRef ws As Excel.Worksheet)
    ws.PageSetup.PrintArea = ws.UsedRange.SpecialCells(xlCellTypeVisible).Address
End Sub