Automatically Sizing PrintArea

[ob3ron02]

I was wondering if anyone could suggest a method to dynamically set
PrintArea on a worksheet according to the last used row (the # o
colums will always be constant).

I know to use -Sheet1-.PageSetup.PrintArea = "-A1:F15-" but i can'
figure out how to find the last row with data in it and get th
appropriate range from that last row.

Thanks,

To

 

[Tom Ogilvy]

If you don't set any printarea, it should do what you want.

but,
Last non-empty cell in column A

Dim rng as Range
set rng = Cells(rows.count,1).end(xlup)
' assume 10 columns wide
set rng = Range(Range("A1"), rng).Resize(,10)

Activesheet.PageSetup.PrintArea = rng.Address(external:=True)