CODEWITHSUNDEEP
excelvba
BarbaraM
BarbaraM

Reputation: 21

Setting a dynamic print area

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

Upvotes: 2

Views: 1190

Answers (3)

VBasic2008
VBasic2008

Reputation: 55073

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

Upvotes: 0

cadvena
cadvena

Reputation: 1093

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"

Upvotes: 2

Darrell H
Darrell H

Reputation: 1886

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

Upvotes: 1

Related Questions