Excel VBA: How to Set Print Area Dynamically (7 Ways)

If you are trying to set a print area dynamically with the help of Excel VBA, then this article may help you for this purpose.
The most advantage of setting print area dynamically is that you can update your dataset from time to time with new entries but each time after updating you don’t have to set your print area. Because after running the codes of this article the print area will automatically set to the newly updated dataset.
So, let’s start with the main article.

Download Workbook

7 Ways to Set Print Area Dynamically Using Excel VBA

Here, we have the following dataset containing the sales records of some of the products for different regions and we want to update this dataset with records of the new products.
So, for obviating the need of setting up the print area every time after updating, we will use the following 7 different ways to set this area dynamically.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

Method-1: Setting Print Area Using a Dynamic Named Range

Here, we will try to set a print area that will contain the following records of the products, and any further records of the newly added products will be automatically included in this area. For this purpose firstly we will create a dynamic named range and then with the help of a VBA code, we will print that range easily.

Step-01:
➤ Go to the Formulas Tab >> Defined Names Group >> Name Manager Option.

Then, the Name Manager dialog box will appear.
➤ Select the New option.

After that, a new wizard New Name will open up.
➤ Choose and write the followings.
Name → Updated_Range
Scope → Workbook
➤Type the following formula in the Refers to box and press OK.

Here, Named Range is the sheet name.

Then, you will be taken to the Name Manager dialog box where you will see the name of your created named range and its corresponding formula.
➤ Press Close.

Step-02:
➤ Go to the Developer Tab >> Visual Basic Option.

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

After that, a Module will be created.

Step-03:
➤ Write the following code

Sub setting_printable_area_1()

Dim sht As Worksheet
Set sht = Worksheets("Named Range")
sht.PageSetup.PrintArea = "Updated_Range"

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet and then, set it to the worksheet Named Range.
Then, the print area is set to the named range Updated_Range and after that, using the WITH statement we have declared different features of the printing area. Finally, we have used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
Then, you will get the preview of our defined print area below.

Now, suppose you want to add the sales records for the new product Potatoes in the dataset.

To prove the dynamicity, run the following code again by pressing F5.

Here, in the Print Preview section, you can see the newly added product and its records in this print area.

Read More: Excel VBA: Set Print Area for Multiple Ranges (5 Examples)

Method-2: Setting Print Area Dynamically Using VBA FIND Function

With the help of the VBA FIND function, we will try to set the print area for the following dataset.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_2()

Dim sht As Worksheet
Dim last_entry As Integer
Set sht = Worksheets("FIND")
last_entry = sht.Cells.Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
sht.PageSetup.PrintArea = Range("B3:D" & last_entry).Address

With sht.PageSetup
       .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet, last_entry as Integer, and then, set it to the worksheet FIND.
last_entry will give the last row number with texts with the help of the FIND function and then, we have set the print area for the range starting from cell B3 to the last cell of Column D.
After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
After that, you will have a preview of our defined print area below.

Read More: How to Set Print Area to Selection Using VBA in Excel (3 Methods)

Method-3: Using SpecialCells Property to Set Print Area Dynamically

In this section, we will use the SpecialCells property for defining the range for which we will set our print area.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_3()

Dim sht As Worksheet
Set sht = Worksheets("SpecialCell")
Range("B3").Select
sht.PageSetup.PrintArea = Range(ActiveCell, _
ActiveCell.SpecialCells(xlCellTypeLastCell)).Address

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet and then, set it to the worksheet SpecialCell and then, cell B3 will be selected so that it will be the active cell.
Using ActiveCell.SpecialCells(xlCellTypeLastCell) we will define the last cell and so the range starting from B3 to the very last cell will be set as the print area.
After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
Finally, you will get the preview of our defined print area as the following.

Read More: How to Change Print Area in Excel (5 Methods)

Method-4: Using Selection Property to Set Print Area Dynamically

Here, we will be using the Selection property to define the range of the print area which will be dependent on our selection.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_4()

Dim sht As Worksheet
Set sht = Worksheets("Selection")
sht.PageSetup.PrintArea = Selection.Address

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet and then, set it to the worksheet Selection and then, using Selection. Address we will set our selected range as the print area.
After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

After saving the code, return to the main sheet.
➤ Select the range, and then, go to the Developer Tab >> Macros Option.

After that, the Macro dialog box will open up.
➤ Select the macro setting_printable_area_4, and then, press the Run option.

Finally, we will obtain a preview of our print area with our selection.

Read More: How to Print Selected Area in Excel (2 Examples)

Similar Readings:

• How to Print to PDF in Excel VBA : With Examples and Illustrations
• Print Landscape in Excel (3 Easy Methods)
• How to Print Labels in Excel (Step-by-Step Guideline)
• Excel VBA Debug Print: How to Do It?
• How to Print Gridlines in Excel (2 Ways)

Method-5: Setting Print Area Dynamically Using VBA UsedRange Property

Utilizing the UsedRange property to define the most updated range we will set this range as the print area in this method.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_5()

Dim sht As Worksheet
Set sht = Worksheets("UsedRange")
sht.PageSetup.PrintArea = sht.UsedRange.Address

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet and then, set it to the worksheet UsedRange and then, using sht.UsedRange.Address we will set the whole range containing any values in this sheet as the print area.
After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
Eventually, you will get the preview of our defined print area as the following, and here also have the heading of the dataset as it is included in the used range.

Read More: How to Print Selected Area in Excel on One Page (3 Methods)

Method-6: Using CurrentRegion Property to Set Print Area Dynamically

Here, we will be using the CurrentRegion property to define the range of the print area to set the print area for the following dataset.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_6()

Dim sht As Worksheet
Dim start_value As Range
Set sht = Worksheets("CurrentRegion")
Set start_value = Range("B3")
sht.PageSetup.PrintArea = start_value.CurrentRegion.Address

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet, start_value as Integer, and then, set it to the worksheet CurrentRegion.
start_value is set to the starting cell of the range which is B3 and then, using start_value.CurrentRegion.Address we will set the whole range containing any values in this sheet as the print area.
After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
After that, you will get the preview of our defined print area as the following.

Related Content: Excel VBA: Print UserForm to Fit on a Page (2 Methods)

Method-7: Setting Print Area Dynamically Using Rows.Count Property

Using the Rows.Count property we will count the last cell of the following dataset and then, using this value we will be able to define the dynamic range for setting the print area.

Steps:
➤ Follow Step-02 of Method-1.
➤ Write the following code

Sub setting_printable_area_7()

Dim sht As Worksheet
Dim last_entry As Integer
Set sht = Worksheets("Rows.Count")
last_entry = sht.Range("B" & Rows.Count).End(xlUp).Row
sht.PageSetup.PrintArea = Range("B3:D" & last_entry).Address

With sht.PageSetup
        .LeftMargin = Application.InchesToPoints(1)
        .RightMargin = Application.InchesToPoints(1)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .Orientation = xlPortrait
        .CenterHorizontally = True
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .Zoom = False
End With
ActiveSheet.PrintPreview

End Sub

Here, we have declared sht as Worksheet, last_entry as Integer, and then, set it to the worksheet Rows.Count.
last_entry will give the last row number with texts with the help of the Rows.Count property and then, we have set the print area for the range starting from cell B3 to the last cell of Column D. After that, using the WITH statement we defined different features of the printing area and finally, we used ActiveSheet.PrintPreview for previewing the print area.

➤ Press F5.
Finally, we will obtain a preview of our print area as the following.

Related Content: How to Set a Row as Print Titles in Excel (4 Methods)

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Conclusion

In this article, we tried to cover the ways to set print area dynamically using Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Print Page Number in Excel (5 Easy Ways)
• Set Print Preview in Excel (6 Options)
• How to Print Graph in Excel (5 Ways)
• Print Excel Sheet with Lines (3 Easy Ways)
• How to Print Multiple Sheets in Excel (7 Different Methods)