[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Set print range depending on # of rows in sheet

Posted on 2010-04-06
6
Medium Priority
?
416 Views
Last Modified: 2012-05-09
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
0
Comment
Question by:AAAAndy
  • 2
  • 2
5 Comments
 
LVL 24

Assisted Solution

by:StephenJR
StephenJR earned 1000 total points
ID: 29925594
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
 

Author Comment

by:AAAAndy
ID: 29927527
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 29933887
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
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 29945774
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
 

Author Comment

by:AAAAndy
ID: 32336270
Fine.
Thanks to both.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

591 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question