Print Macro

P

Pam

Trying to write a macro to print a spreadsheet where the
number of rows of data varies.

What's wrong with this code?

Dim rng As Range
Set rng = Range("A1").End(xlDown).End(xlToRight)
With ActiveSheet.PageSetup
.PrintArea = rng
End With

Thank you.
Pam
 
R

Ron de Bruin

Hi Pam

Leave your PrintArea empty
Excel will print all cells with a value on the sheet
 
S

stefano gatto

When I record this I get a string assigned to .printarea
not an object...
Try to append .address to rng before assigning it.

Stefano
 
G

GB

Ron is of course right. However, you may be interested for future reference
in why your macro was not working.

As far as I can see, your set range statement just sets one cell (bottom
right of the spreadsheet).

Try this instead:-

Set rng = Range("A1", Range("A1").End(xlDown).End(xlToRight))

Geoff
 
G

GB

In fact, unless you need rng for later, why not simplify it to this:

ActiveSheet.PageSetup.PrintArea = _
Range("A1", _
Range("A1").End(xlDown).End(xlToRight)).Address

Regards

Geoff
 
R

Ron de Bruin

Use this then Pam, GF

Sub test()
With ActiveSheet.PageSetup
.PrintArea = ActiveSheet.UsedRange.Address
End With
End Sub
 
Top