Print an Excel VBA Used Data Range One Page Wide
Question details
Create a VBA macro that prints the range from A1 to column D’s last populated row, using landscape orientation and one-page-wide scaling.
- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- The user needs to print a variable-size worksheet data area without manually setting the print range each time.
- Observed behavior
- The desired result is for A1:D[last populated row in column D] to print in landscape orientation and fit across one page wide.
Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) and enable macros when opening it in the desktop app. Confirm that column D is populated for every row that should be included in the print area.
Use VBA to set the print area and fit it to one page wide
Use this macro when the printable data always begins at A1 and ends in column D, but the number of populated rows changes.
The macro finds the last nonblank cell in column D, assigns A1 through that row as the print area, switches the sheet to landscape orientation, and prints it one page wide. The output may span multiple pages vertically.
In Excel, press Alt+F11, then select Insert > Module to create a standard VBA module.
Paste the following code into the module: Sub prt() Dim LR As Long Dim ws As Worksheet Dim rngPrint As Range Set ws = ActiveSheet LR = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row Set rngPrint = ws.Range("A1:D" & LR) With ws.PageSetup .PrintArea = rngPrint.Address .Orientation = xlLandscape .Zoom = False .FitToPagesWide = 1 .FitToPagesTall = False End With rngPrint.PrintOut End Sub
Return to the worksheet, press Alt+F8, select prt, and click Run. Excel sends the defined range to the selected printer.
To preview the result before printing, replace rngPrint.PrintOut with rngPrint.PrintPreview, or open File > Print after running the macro.
Print a Dynamic Data Range in WPS Spreadsheets
WPS Spreadsheets provides familiar worksheet printing controls and macro tools for compatible Excel workbooks. Open the macro-enabled workbook, confirm the print area and landscape scaling, then run or adapt the macro for the active sheet.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the XLSM or XLSX workbook containing the data.
- 2. Check the print range: Select A1 through the final row in column D, then use Page Layout > Print Area > Set Print Area if you want to verify the target range manually.
- 3. Set landscape and one-page-wide scaling: Go to Page Layout and choose Orientation > Landscape, then set the page scaling option to fit the sheet to one page wide.
- 4. Run or add the macro: Open the Developer or Macro tools available in your WPS Spreadsheets installation, add the VBA-compatible procedure if needed, and run prt for the active worksheet.
Frequently Asked Questions
Why does the macro use column D to find the last row?
The line ws.Cells(ws.Rows.Count, "D").End(xlUp).Row searches upward from the bottom of column D and returns its last nonblank row. Use another column letter if a different column is more reliably populated.
How can I preview the pages instead of printing immediately?
Replace rngPrint.PrintOut with rngPrint.PrintPreview. You can then inspect the landscape layout and one-page-wide scaling before sending the document to a printer.
Will this macro force the entire worksheet onto one physical page?
No. FitToPagesWide = 1 limits the output to one page across. FitToPagesTall = False allows additional pages vertically when there are many rows.
Why is Zoom set to False in the PageSetup block?
Excel uses FitToPagesWide and FitToPagesTall only when manual zoom is disabled. Setting .Zoom = False ensures the one-page-wide setting is applied.
Can I use this macro in a cloud-stored workbook?
Yes, if you open the workbook in the desktop version of Excel and enable macros. Browser-based Excel does not run VBA macros.
