Set printing area In Excel 2013 using macro

excel, vba

Solution

It's easier to see what is happening if you declare a few variables and decompose your statement.

Try this:

Sub SetPrintArea()
  Dim ws As Worksheet
  Dim lastRow As Long

  Set ws = ThisWorkbook.Sheets("Tags")

  ' find the last row with formatting, to be included in print range
  lastRow = ws.UsedRange.SpecialCells(xlCellTypeLastCell).Row

  ws.PageSetup.PrintArea = ws.Range("A2:L" & lastRow).Address
End Sub

Alternatively, if you want to find the lastRow with data, you can find the lastrow like this:

lastRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Note that the 65536 value you're using as the starting point to find the last row is obsolete (although it will frequently still work) as of Excel 2007, which has over a million rows per sheet.

A few things to note about your approach:

- `Cells(2,1)` is A2. The syntax is `Cells([row], [column])`

- You want the last populated row in column L, but are looking in column A instead. `Range("A65536").End(xlUp).Row`

This results in a print area (once you've added the .Address to your range) of A1:L2. Why A1? Because the column A is empty, and the lastrow is therefore row 1. You have set the range to be A2:L1, which becomes A1:L2.

Problem

In Excel 2013, having sheet named "Tags", I am trying to set a printing area from `A2` till end of page, ending with column `L`. ``` Worksheets("Tags").PageSetup.PrintArea = Worksheets("Tags").Range( _ Cells(2, 1), Cells(Worksheets("Tags").Range("A65536").End(xlUp).Row, 12)) ``` My code compiles okay, but it does not seems to work - no printing area has been set. What should be a correct macro to set printing area?

Original source