Setting a dynamic print area

user_15363527 0
Viewed 394

I am trying to define the print area for the active sheet that would include all rows in columns A thru X.

The number of rows changes with each active sheet.

This gives me a runtime error and thinks I'm trying to write a formula.

Sub setprintarea()
' Sets Range from cell A1 for all rows thru column 24 (X)
ActiveSheet.PageSetup.PrintArea = "$A$1:$X$"
End Sub
vba excel
3 Answers

You have a minor syntax error. To specify full columns, you will want to specify "$A:$X". This may give the impression that you will print the entirety of columns A through X. However, that is not the case. Excel will print only enough sheets as needed for cells containing content.

As an example, create a new worksheet. On the worksheet, enter values in random cells in columns A through C. Next, set the print area to $A:$B. Now, do a print preview. You will see that Excel is smart enough to only create pages where data exists.

Corrected code:

ActiveSheet.PageSetup.PrintArea = "$A:$X"
user_31797730

It is true that the OP probably doesn't want the range to extend to the last row on the sheet. It is also possible that X is not the longest column on the sheet, in which case you would want to test each column to determine the sheet length. Hope this doesn't overcomplicate the matter, but sometimes the last column isn't the sheet length.

MaxLastRow = 1
For x = 1 To 24
    If ActiveSheet.Cells(Rows.Count, x).End(xlUp).Row > MaxLastRow Then
        MaxLastRow = ActiveSheet.Cells(Rows.Count, x).End(xlUp).Row
    End If
Next x

ActiveSheet.PageSetup.PrintArea = "$A$1:$X$" & MaxLastRow
user_64666940

Dynamic Print Area

Sub setPrintArea()
    With ActiveSheet
        .PageSetup.PrintArea = Intersect(.UsedRange, .Columns("A:X")).Address
        ' Write the address to the Immediate window (CTRL+G).
        Debug.Print .PageSetup.PrintArea
    End With
End Sub
user_98140690
Related