CODEWITHSUNDEEP
vbaexcelprintingpage-break
graham.reeds
graham.reeds

Reputation: 16476

Hiding Page Breaks in Excel

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.

Upvotes: 2

Views: 2228

Answers (2)

Ejaz Ahmed
Ejaz Ahmed

Reputation: 688

Excel, by default prints only visible cells. You wouldnt have to set the Print area to visible cells as previously suggested.

I suppose you have un-checked the Print Object option in all of the Check-boxes. And that area appears blank, when you print the sheet. If that is the case, read on:

I suggest writing a simple macro that hides the rows that contain the check boxes. Set the visible property of the check boxes to false. Print the sheet, and then return everything back to the original state.

Alternatively, you could just select the rows above the check boxes, then the entire area below the check boxes, and then set that as the PrintArea. That should solve the problem you are having.

Also, I may have wildly misunderstood the situation, in which case, I am sorry I wasted your time.

Upvotes: 0

Oorang
Oorang

Reputation: 6770

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

Upvotes: 1

Related Questions