VBA & Macro Problems

How to Set a Dynamic Print Area and Print Button in Excel using VBA

Muhammad Talha Oct 20, 2026 869 views

Question details

The user wants to automate the printing process in Excel by using a VBA macro that sets a dynamic print area adapting to the final row of data, and triggering it via a worksheet button.

How to Set a Dynamic Print Area and Create a Print Button using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Automating print setups for a dataset with a variable number of rows, ensuring the print area covers columns A through I, fits on one landscape page width, repeats the header row, and prints automatically.
Observed behavior: Creating a VBA macro to determine the last row dynamically, applying specific page layout settings, printing the active sheet, and mapping this macro to a Form Control button.

Before you start

Ensure you have enabled the Developer tab in your spreadsheet software to access the VBA Editor and Form Controls, and save your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Create the VBA Macro for Dynamic Print Area

Write a VBA script that finds the last used row, configures the page setup, and prints the active worksheet.

This macro dynamically calculates the last row containing data in column A to ensure no blank pages are printed. It also configures standard print settings like landscape orientation, repeating header rows, and scaling.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a New Module

In the VBA editor, click 'Insert' from the top menu and select 'Module'.

Paste the Macro Code

Copy and paste the following code into the module window: Sub PrintTest() Dim trow As Long trow = Cells(Rows.Count, 1).End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & trow With ActiveSheet.PageSetup .PrintTitleRows = "$2:$2" .Orientation = xlLandscape .FitToPagesWide = 1 .FitToPagesTall = 1 End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False End Sub

Save the Macro

Close the VBA editor by clicking the 'X' in the top right corner. Ensure you save your file as an Excel Macro-Enabled Workbook (.xlsm).

Create the VBA Macro for Dynamic Print Area

Macro Ready: Your macro is now successfully stored in the workbook and can be executed to set the print area and print the sheet automatically.

Solution 2

Assign the Macro to a Form Control Print Button

Insert a clickable button directly onto the worksheet so users can execute the print macro instantly without opening the developer tools.

Advanced Spreadsheet Automation

Automate Printing with Macros in WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros. You can easily create dynamic print areas, automate page setups, and insert form controls to streamline your daily workflow.

1. Open WPS Spreadsheet: Launch WPS Office and open your dataset in WPS Spreadsheet.
2. Access the VBA Editor: Navigate to the Developer tab and click 'VBA Editor' to insert your dynamic print area code.
3. Insert a Form Button: Use the 'Insert' option within the Developer tab to add a button and assign your print macro instantly.

Fully compatible with Microsoft Excel VBA macros (.xlsm).Easily configure dynamic print areas and page layouts.Insert Form Control buttons for one-click macro execution.Free, lightweight, and user-friendly spreadsheet solution.

microsoft office alternative - wps office

Frequently Asked Questions

How do I dynamically find the last used row in a specific column using VBA?

You can find the last used row in column A by utilizing the VBA snippet: `Cells(Rows.Count, 1).End(xlUp).Row`. This searches from the very bottom of the sheet upwards to find the last cell with data.

Can I change the columns included in the dynamic print area?

Yes. In the VBA code line `ActiveSheet.PageSetup.PrintArea = "$A$1:$I$" & trow`, you can change the letter "I" to whichever column letter represents the last column you want to include in your print area.

Why is my Excel macro not running when I click the button?

Macro execution might be restricted by your software's security settings. Go to Options > Trust Center > Trust Center Settings > Macro Settings, and ensure macros are enabled. Also, make sure your file is saved as a Macro-Enabled Workbook (.xlsm).

How can I fit all columns onto a single page width when printing via VBA?

Within your VBA macro's `With ActiveSheet.PageSetup` block, set `.FitToPagesWide = 1`. To allow the rows to span across multiple pages without being squished, set `.FitToPagesTall = False`.

Excel & Data Problems