CODEWITHSUNDEEP
excelvba
Ravi
Ravi

Reputation: 115

Setting the print Area in VBA to current cell in a column

I want to include just one column from a spreadsheet in print area but users can add a lot of rows so the column can grow dynamically. I want that column to be entirely included in print area. So, if say 10 rows were added, it would print those new 10 rows too. How would I do that in Excel VBA? There seem to be several methods of going about it from what I saw on the web but I am using this for the first time so I would appreciate your input on this. Thank you.

Upvotes: 0

Views: 658

Answers (1)

Excel Hero
Excel Hero

Reputation: 14764

You do not need VBA for this.

Here is what to do.

  1. Switch to the sheet you wish to have the dynamic print range on, say Sheet1.

  2. Open the Name Manager with Control-F3.

  3. Click the New button at the top.

  4. For the Name field enter: print_area

  5. For the Scope field select Sheet1 (or whichever sheet you are on).

  6. For the Refers To field paste this formula:

    =$A$1:INDEX($A:$A,MAX(IFERROR(MATCH({"*";9E+99},$A:$A,{-1;1}),1)))

  7. Click OK and then Close

That's it.

Now as column A is added to the print range will include all of column A and nothing else... for that sheet only.

Note: If you want this for a different column than column A, simply change the A's in the formula to the different column label.

Upvotes: 1

Related Questions