Set print range depending on # of rows in sheet
Posted on 2010-04-06
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):
ActiveSheet.PageSetup.PrintArea = "$A$1:$C$" & Range("MaxRow").Value
...where MaxRow is a value calculated in the sheet to identify the bottom row.
I also tried this (in a module):
Dim VP3Range As String
VP3Range = "$A$1:$C$" & Range("VP3_MaxRow").Value
' 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