Print Range for Dynamic Print Area

[shellp]

Hello

I am using MS-Excel 2010.

I have a worksheet where rows are visible/hidden based on user selection. I would like to add a "print" button that can be based on this changing size. How do I do that? Thank.

 

[Joe4]

I am not sure I understand.
By default, it will not print out any hidden sections. So in theory, you shouldn't have to change anything.

If there is more to it than this, you will need to provide us with more detail, maybe walk us through some actual examples.

 

[shellp]

Hi

Thanks for the speed response. The full range with all rows visible is D2:N90 and it goes to 2 pages. If some rows are hidden then page 2 is blank.

 

[craigexcel]

Is the 2nd page blank, perhaps because there may be a set print range for the worksheet, or, have page breaks been inserted?

 

[Joe4]

Is there data in row 90 and column N?

Also note that if the last column is wider than it needs to be, it could be causing the "width" to spill over on a new page.

 

[shellp]

Hi

It's actually not the width that is the problem, it is the height. I think what I want is to be able to calculate the number of visible rows and format accordingly i.e. if totalsrow = 50 then no page breaks, if >50 then go to page 2, header is D2:N8 etc. Is that possible/feasible? Thanks.

 

[Joe4]

It's actually not the width that is the problem, it is the height. I think what I want is to be able to calculate the number of visible rows and format accordingly i.e. if totalsrow = 50 then no page breaks, if >50 then go to page 2, header is D2:N8

It should be able to handle it on its own without issue.

What is your print range currently set at? Is it D2:N90?
If so, what is your REAL last row with data? Is it row 90?
If not, are all the rows between your last row with data and row 90 hidden?

If not, then the issue is probably that you have blank rows of data at the end of your print range that are pushing it onto a second sheet.
The way to address that would be to hide those blank rows also, or dynamically set your print range to end at the real last row of data.

 

[shellp]

Hi Joe

Thanks for sticking with me on this. Okay, the actual last row could change. If all showing it is from D2 to N88 so I changed that in my print range. But for some selections only rows 2 to 40 are visible and row 88 isn't but it is still going to page 2 though page 2 is blank. Suggestions? Thanks.

 

[Joe4]

Is there some column that will always have data in it?
If so, then we can use that to dynamically determine the last row to print and set the print area.

So, if it was column D, and we want to set the print area starting in cell D2, going out to column N and down to our last row, it would look something like this:

    Dim lr As Long
    
'   Find last row in column D with data
    lr = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Set print area
    ActiveSheet.PageSetup.PrintArea = "$D$2:$N$" & lr

 

[shellp]

Thanks Joe. This worked with a tweak: I had to first clear the print range.

Private Sub cmdPrint
Dim sh as Worksheet
Di lr as long

lr=cells(rows.count,"D").End(xlUp).row

Set sh = sheets("MyWS")
sh.PageSetUp.PrintArea=""
Sh.Range("$D$2:$N$" & lr).select
Applications.Dialogs(xlDialogPrint).show
end sub