Set print Range
 

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

Set print Range
 

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...g_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

"PC" <paulm DOT c at iol DOT ie wrote in message
...
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

Set print Range
 

Thanks Dick, Much appreciated

"Dick Kusleika" wrote in message
...
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...g_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

"PC" <paulm DOT c at iol DOT ie wrote in message
...
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