Set print area that is variable

**philfox** · 03-30-2010, 07:10 AM

Hi All

I have a spreadsheet, the number of columns of which are fixed, but the number of rows vary depending on data inputted.

In cell R5, the number of the last row containing relevant data, is calculated and displayed (say "x")

All I want to do, using VBA, is to set the print area to A1:P(X). I know this must be simple - but not for me!!

Thanks for any help.

Phil

**Domski** · 03-30-2010, 07:34 AM

Something like this:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Dom

**teylyn** · 03-30-2010, 07:40 AM

No need for VBA. Just change the definition of the Default name PrintArea.

Click Insert - Name - Define

If PrintArea already exists, click it. If not, create the name. Replace existing definition with / Use the formula

=$A$1:index($P:$P,match("ZZZ",$R:$R,1))

This range will now shrink and grow according to the last text cell in column R.

**philfox** · 03-31-2010, 05:22 AM

Thanks guys. Solved it using the first suggestion, so didn't go any further, but will bear in mind the other offerings for future use.

**teylyn** · 03-31-2010, 06:00 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**teylyn** · 03-31-2010, 06:00 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save