Link to home
Start Free TrialLog in
Avatar of AAAAndy
AAAAndy

asked on

Set print range depending on # of rows in sheet

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

...where MaxRow is a value calculated in the sheet to identify the bottom row.

I also tried this (in a module):

Sub SetPrintArea()
Dim VP3Range As String
VP3Range = "$A$1:$C$" & Range("VP3_MaxRow").Value
 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
SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AAAAndy
AAAAndy

ASKER

Thanks, Steven.
The solution you propose looks like it will work, however it would be slower than using a variable like MaxRow, as I described. The model is getting large, and every millisecond counts.
Of course if I can't make it work that way at all, I'll give your solution a try.
I'd really like to substitute where you have 'n' with my value MaxRow.  But I can't get it to accept the concatenated string as the range address for the PrintArea property.
Perhaps it has something to do with the value of MaxRow being a number vs. a string, but I can't figure that out. Maybe I'm Dimensioning it incorrectly.
Andy
Andy - not sure what you mean. 'n' is a variable, just a number but otherwise along the lines of the method you outlined, and I can't see why it should be slow.
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AAAAndy

ASKER

Fine.
Thanks to both.