Set print Range

[PC]

Hi,

I'm trying to set the print range of a report. The code I'm using is a
follows

Dim Print_Range As Range
Print_Range = Range("A1:E1", Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = Print_Range
End Sub

This isn't working though with a Run-time error 91, Object Variable or With
block variable not set.

Any ideas

Paul

 

[Dick Kusleika]

Paul

When you assign a range, you have to use the Set keyword and don't use the
Select method

Set Print_Range = Range("A1:E1", etc...

The PrintArea property of the PageSetup object takes a string, not a range,
so that would look like

ActiveSheet.PageSetup.PrintArea = Print_Range.Address

See here

http://www.dicks-blog.com/excel/2004/05/setting_the_pri.html

I would do it like this

Dim Print_Range As Range

With ActiveSheet
Set Print_Range = .Range("A1", .Range("A1").End(xlDown).Offset(0,4))
.PageSetup.PrintArea = Print_Range.Address
End With

 

[PC]

Thanks Dick, Much appreciated

Paul

When you assign a range, you have to use the Set keyword and don't use the
Select method

Set Print_Range = Range("A1:E1", etc...

The PrintArea property of the PageSetup object takes a string, not a range,
so that would look like

ActiveSheet.PageSetup.PrintArea = Print_Range.Address

See here

http://www.dicks-blog.com/excel/2004/05/setting_the_pri.html

I would do it like this

Dim Print_Range As Range

With ActiveSheet
Set Print_Range = .Range("A1", .Range("A1").End(xlDown).Offset(0,4))
.PageSetup.PrintArea = Print_Range.Address
End With

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com