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

Get FREE Advanced Excel Exercises with Solutions!

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 be set to the newly updated dataset.
So, let’s start with the main article.


How to Set Print Area Dynamically Using Excel VBA: 7 Ways

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.

Excel VBA set print 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.

Excel VBA set print area dynamically

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

dynamic named range

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

Excel VBA set print area dynamically

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.

=OFFSET('Named Range'!$B$3,0,0,COUNTA('Named Range'!$B:$B),COUNTA('Named Range'!$3:$3))

Here, Named Range is the sheet name.

  • COUNTA(‘Named Range’!$B:$B) → COUNTA will return the total number of non-blank cells in Column B as the Height
    Output → 10
  • COUNTA(‘Named Range’!$3:$3) → COUNTA will return the total number of non-blank cells in Row 3 as the Width
    Output → 3
  • OFFSET(‘Named Range’!$B$3,0,0,COUNTA(‘Named Range’!$B:$B),COUNTA(‘Named Range’!$3:$3)) becomes
    OFFSET(‘Named Range’!$B$3,0,0,10,3) → OFFSET returns the range starting from cell $B$3 with a height of 10 and width of 3.
    Output → $B$3:$D$12

dynamic named range

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.

dynamic named range

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

Excel VBA set print area dynamically

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

dynamic named range

After that, a Module will be created.

dynamic named range

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.

dynamic named range

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

Excel VBA set print area dynamically

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

dynamic named range

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

Excel VBA set print area dynamically

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

dynamic named range

Read More: Excel VBA: Set Print Area for Multiple Ranges


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.

Excel VBA set print area dynamically

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.

FIND Function

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

FIND Function


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.

Excel VBA set print area dynamically

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.

SpecialCells Property

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

SpecialCells Property


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.

Excel VBA set print area dynamically

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.

Selection Property

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

Selection Property

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

Selection Property

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

Excel VBA set print area dynamically

Read More: How to Set Print Area to Selection Using VBA in Excel


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.

Excel VBA set print area dynamically

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.

UsedRange Property

➤ 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.

UsedRange Property


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.

Excel VBA set print area dynamically

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.

CurrentRegion Property

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

CurrentRegion Property


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.

Excel VBA set print area dynamically

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.

Rows.Count Property

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

Rows.Count Property


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.

Practice


Download Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo