Setting printarea through range object

[Berend Botje]

Hi,

I have a worksheet in which I have hidden all of the empty rows. Now
want to print the visible rows, but I don't seem to be able to.

I prefer selecting the rows and storing them in a range object, afte
which I can set the range object as the printarea. Can this be done an
if so, what code should I use?

Yours truly,

Berend Botj

 

[Berend Botje]

Doesn't anybody know how to do this

 

[Norman Jones]

Hi Berend.

I have a worksheet in which I have hidden all of the empty rows. Now I
want to print the visible rows, but I don't seem to be able to.

Hidden rows do *not* print so you should have no problem.

I prefer selecting the rows and storing them in a range object, after
which I can set the range object as the printarea. Can this be done and
if so, what code should I use?

Certainly. For example:

Sub SetPrintArea()
Dim Rng As Range
' Set your range to suit
Set Rng = Range("A1:H3,A24:H25")
ActiveSheet.PageSetup.PrintArea = Rng.Address
End Sub

 

[Berend Botje]

First of all, thanks for reacting.

Can I do the range thing with R1C1 coordinates? I can't get it to wor
properly with multiple range selections. For example I want to selec
columns 1 through AK of rows 1 through 200 and 250 through 400. How ca
I do this

 

[Norman Jones]

Hi Berend,

Try:

Set rng = Range("A1:AK200,A250:AK400")

 

[Tom Ogilvy]

Setting a multirange printarea will result in multiple print jobs. Since
you are hiding the rows you don't want to print, you should set the print
area to one contiguous range and hide the rows you don't want printed --
unless you want multiple print jobs.

 

[Berend Botje]

I found the problem.... I want to print the first 8 rows on every page
That is why all rows (including the hidden ones) are printed. Since
have over 10000 rows, this is most unwanted. Does anyone know how t
solve this

 

[Tom Ogilvy]

I couldn't reproduce that using Rows to Repeat at top. If I had hidden rows
in the specified rows, they were not printed.

 

[Dave Peterson]

I've never seen hidden rows print.

But if I added manual page breaks, I could see the rows to repeat at top show up
for those pages that have nothing but hidden rows.

I'd either remove the manual page breaks or copy the worksheet and delete the
hidden rows (and print from there).