Set print range depending on # of rows in sheet

[AAAAndy]

I want to set a print range depending on how many rows in the sheet have content. The range ALWAYS starts at A1 and goes to column C, but the bottom row is variable.
I thought the following code would do the trick (in the Excel worksheet section of VB):

Sub SetPrintArea()
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$" & Range("MaxRow").Value
End Sub

I also tried this (in a module):

Sub SetPrintArea()
Dim VP3Range As String
VP3Range = "$A$1:$C$" & Range("VP3_MaxRow").Value
' VP3_MaxRow=6
MsgBox VP3Range
' The msgbox clearly shows $A$1:$C$6

'The following line causes a Run-time error '9': Subscript out of range
Sheets("Value Plan_3").PageSetup.PrintArea = VP3Range
End Sub


Please help!
Andy

 

[Atroxell]

I'm not sure from what you have shown where the value for

Range("VP3_MaxRow").Value

is getting a 6.... Maybe I'm missing something, but I suspect the value is leftover from something else.

But I would use:

VP3Range = "$A$1:$C$" & Range("C65536").End(xlUp).Row

to define the area.

 

[AAAAndy]

I have formulas in the sheet that tell me what row is the last row in the range. That's where the 6 comes from - I'm just using that as an example. That's not the problem.

As I said in my post, when the code gets to:
MsgBox VP3Range
it shows the text exactly as it should be:
$A$1:$C$6

For some reason I'm having trouble assigning that value to the printarea.

Thanks for you response.