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.Prin tArea = "$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.PrintAr ea = VP3Range
End Sub
Please help!
Andy
I thought the following code would do the trick (in the Excel worksheet section of VB):
Sub SetPrintArea()
ActiveSheet.PageSetup.Prin
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.PrintAr
End Sub
Please help!
Andy
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fine.
Thanks to both.
Thanks to both.
ASKER
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