VBA & Macro Problems

How to Set an Excel Print Area from a Table Using VBA (Including Row Above)

Amos Gikunda Oct 10, 2026 870 views

Question details

The user needs to define a print area in Excel via VBA that encompasses a specific table and the row immediately above it, while avoiding the VBA error 1004 triggered by using RefersToRange on a table name.

How to Set an Excel Print Area from a Table Using VBA

Product: Microsoft Excel
Device & OS: not provided
Scenario: Automating the definition of print areas using macros when the target data is structured as an Excel Table (ListObject) rather than a standard named range.
Observed behavior: Using the RefersToRange property on a name that refers to an Excel table throws VBA error 1004. A different method is required to successfully reference the table's range.

Before you start

Verify that your target table is not located in the very first row (Row 1) of your worksheet, as the macro will attempt to reference the row above it; referencing row zero will result in a runtime error.

Solution 1Recommended

Use ListObjects with Offset and Resize

Use the table's ListObject and Range properties combined with Offset and Resize to dynamically define the print area without triggering Error 1004.

Because an Excel table is treated as a ListObject rather than a standard defined Name, attempting to use RefersToRange on it will cause an error. Instead, you can grab the table's range directly through the ListObjects collection, offset it by one row upwards, and resize it to include the extra row.

Open the VBA Editor

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

Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank script window.

Input the VBA Code

Paste the following code: Sub SetPrintRange() Dim tbl As Range Dim printMe As Range With ActiveSheet Set tbl = .ListObjects("Z1NamedRangePriceList").Range Set printMe = tbl.Offset(-1).Resize(tbl.Rows.Count + 1, tbl.Columns.Count) .PageSetup.PrintArea = printMe.Address End With End Sub

Execute the Macro

Modify "Z1NamedRangePriceList" to match your table's actual name. Press F5 or click the 'Run' button to apply the new print area to your active sheet.

Table Naming: Ensure you are using the actual Table Name found in the 'Table Design' tab, not a manually created Named Range.

Solution 2

Use the Union Method to Combine Ranges

Alternatively, you can define the row above the table and the table itself as two separate ranges, then combine them using the Union function.

Run VBA Macros Flawlessly with WPS Spreadsheet

WPS Spreadsheet provides excellent support for VBA macros and advanced ListObject automation. You can easily execute your Excel VBA scripts to automate print areas, manage tables, and streamline your workflow in WPS Office.

1. Download and Install WPS Office: Get the latest version of WPS Office and ensure the VBA module is enabled.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your .xlsm or .xlsx file containing the table.
3. Open the VBA Editor: Go to the 'Tools' tab and click on 'VBA Editor' to access the macro environment.
4. Run the Print Area Code: Paste your ListObject macro and click 'Run' to instantly update your print layout.

Full support for VBA (Visual Basic for Applications) macrosSeamlessly handles Microsoft Excel formats (.xlsm, .xlsx)Advanced print area and page layout management built-inFree, lightweight, and fast installation

microsoft office alternative - wps office

Frequently Asked Questions

Why does RefersToRange cause VBA Error 1004?

VBA Error 1004 occurs because RefersToRange expects a standard defined 'Name' object (a Named Range). When the name actually refers to an Excel Table, VBA requires you to reference it as a ListObject instead.

What happens if my Excel table is in the first row?

If your table starts in row 1, using Offset(-1, 0) tells Excel to look for 'Row 0', which does not exist. This will result in a runtime error. Ensure there is at least one blank row or header row above your table before running this macro.

Can I use these VBA scripts in WPS Office?

Yes. WPS Office supports VBA macros. You can open the VBA Editor from the Tools tab in WPS Spreadsheet and run standard ListObject and Range scripts just like you would in Excel.

Excel & Data Problems