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
AAAAndyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
Does this work?
Sub SetPrintArea()

Dim n As Long

With ActiveSheet
    n = .Cells(Rows.Count, "C").End(xlUp).Row
    .PageSetup.PrintArea = "$A$1:$C$" & n
End With

End Sub

Open in new window

0
AAAAndyAuthor Commented:
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
0
StephenJRCommented:
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.
0
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Andy,

there is also a macro-free solution to this. If it does not already exist, create a named range called PrintArea and let it refer to

=Sheet2!$A$1:INDEX(Sheet2!$C:$C,MATCH("ZZZ",Sheet2!$A:$A,1))

or if the values in column A are numeric:

=Sheet2!$A$1:INDEX(Sheet2!$C:$C,MATCH(99^99,Sheet2!$A:$A,1))

cheers, teylyn
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AAAAndyAuthor Commented:
Fine.
Thanks to both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.