1

So, I am trying to create a VBA code that will automatically change the print area of an excel worksheet to the last row with data. Currently, I have a way to record the last row of the sheet, but not a way to convert that value into the end of the range. My issue is with the second part of this code. The idea I have is to make a string variable called "Test" combine the value of the variable last_row with the fixed column O.

Currently, the row ends at 400, so the print area would be $B$2:$O$400 I need the VBA script to know when more rows have been added and adjust the print area to be that new figure. example: $B$2:$O$434

Dim last_row as long

Sheets ("Main Tab").Select

last_row = cells(Rows.Count,1).end(xUp).Row

Dim Test as String

Test = "O$" & last_row

ActiveSheet.PageSetup.PrintArea = "$B$2:test"
Harun24hr
  • 30,391
  • 4
  • 21
  • 36
Mike
  • 11
  • 2
  • ActiveSheet.PageSetup.PrintArea = "$B$2:" & test – Harun24hr Feb 23 '21 at 05:59
  • `xUp` needs to be `xlUp` the lower case `L` is missing here! Make sure you use `Option Explicit` so you get notified about such issues. Also I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to get gid of your `Select` statement and the `ActiveSheet` too. – Pᴇʜ Feb 23 '21 at 09:03

1 Answers1

1

If your other things are okay then problem is in putting test inside double quote. So, change it like

ActiveSheet.PageSetup.PrintArea = "$B$2:" & test
Harun24hr
  • 30,391
  • 4
  • 21
  • 36