1

I have the width for the range I want correct, but am having issues dynamically adjusting the height for print preview. The number of rows for a weekly report adjusts weekly, so I'd like a way to create a Named Range that updates height automatically. Problem is, there are a number of blank rows mixed in there, and I want a way where I can find the very last value in Column A, even though there are blanks in between A1:A(whatever the last row is). Below is an example and my range formula, and if I attempt to print, the table is cut halfway through.

enter image description here

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),12)

I attempted to put a white font in the blank column A cells, but have no luck. Thanks!

Improve this question

3 Answers 3

Reset to default
1

You should be able to use this formula to get what you need (use this formula under Name Manager with the name print_area scoped to the worksheet in question): =$A$1:INDEX($D$1:$D$1000,MATCH(9.99999999999999E+307,$A$1:$A$1000))

  • $A$1 is what I call the anchor cell. This is the top left cell you want to print;
  • $D$1:$D$1000 is going to be the column to the far right that should "complete" your area (change D to the appropriate column and change 1000 to be a row that is farther down than your report would ever stretch); and
  • The MATCH function is attempting to find the largest possible value in $A$1:$A$1000. This only works for numerical data. Be sure to use a column that'll have data in your last row (you stated that there may be blanks along the way).

CAUTION:

If you go this route, I highly recommend that you make the comment of the defined name something like:
Should equal: =$A$1:INDEX($D$1:$D$1000,MATCH(9.99999999999999E+307,$A$1:$A$1000)).
The reason being that if you go messing with the margins or print titles or something else, it'll likely reset the print_area and you'll have to start over.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Holy moley that worked! Thank you kind stranger! I hid the largest value on the last line, by making it in white font. So even if I added rows to the table, it would print everything in between.
I'm so glad it worked for you! The formula will work even if your very last value in the given column is something like 1. The MATCH function is written in a way to find the last value in the given range (in this case, $A$1:$A$1000), even if it isn't the largest value in the whole column.
0

You can use this to find the last row in column A. Since it searches from the bottom up, the blank rows above the last cell will not effect it.

LastRowColA = Worksheets(1).Columns(1).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row
Improve this answer

Comments

0

Are you wanting a VBA solution? Your tag says VBA, but your formula is an Excel formula. If VBA, normally I just get the address of the ListObject (i.e. the Table) and extract the last row from it. Something like this:

With Sheet2

    .PageSetup.PrintArea = .Range("$A$1:" & .ListObjects("Table1").DataBodyRange.Cells(.ListObjects("Table1").DataBodyRange.Cells.Count).Address).Address

End With
Improve this answer

1 Comment

I have information outside of a table, so I wanted to avoid just printing the ListObject. The VBA comes in when I wanted to set the PrintArea of a Named Range, but I was having issues getting the last row to be viewed with the current formula I have.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.