VBA & Macro Problems

How to Fix Excel VBA Selecting an Extra Column in a Range

Huda QurayshiHuda Qurayshi Oct 29, 2026 872 views

Question details

The user needs to correct an Excel VBA macro that unintentionally selects an extra column when determining a dynamic range via Offset and Resize.

How to Fix Excel VBA Selecting an Extra Column in a Range
Product
Excel
Device & OS
not provided
Scenario
Running a VBA macro to select and clear a specific range of columns, but the Offset and Resize calculations are causing the selection to exceed the intended bounds.
Observed behavior
The VBA code selects one column more than intended (e.g., T:AQ instead of T:AP) because the column count specified in the Resize method mismatches the exact width from the Offset starting point.
Before you start

Before editing your VBA macro, make sure to save a copy of your workbook (.xlsm) to prevent any accidental data loss if the code modifies the wrong range during testing.

Solution 1Recommended

Define Range Directly and Avoid Select

The most reliable way to handle dynamic ranges in VBA is to bypass the error-prone 'Offset', 'Resize', and 'Select' methods by explicitly defining the exact start and end columns.

Relying on Select, Selection, and ActiveCell can make your macros run slower and behave unpredictably. Instead of calculating offsets, you can directly build the range address by finding the last row and concatenating the exact column letters you wish to target.

1
Open the Visual Basic Editor

Press 'Alt + F11' in Excel to open the VBA Editor, and locate the module containing the problematic macro.

2
Disable Screen Updating

At the beginning of your Sub routine, add 'Application.ScreenUpdating = False' to speed up execution and prevent screen flickering.

3
Define the Exact Range and Clear It

Use explicit range referencing. For example, to clear columns T through AP down to the last used row in AP, use: Dim lastRow As Long With Sheets("Your Sheet Name") lastRow = .Range("AP9").End(xlDown).Row .Range("T9:AP" & lastRow).Clear End With

4
Re-enable Screen Updating

At the end of your Sub routine, add 'Application.ScreenUpdating = True' to restore normal Excel behavior.

Define Range Directly and Avoid Select
Best Practice: Directly addressing the range like .Range("T9:AP" & lastRow).Clear executes much faster and eliminates the off-by-one errors common with Resize parameters.

Edit and Run VBA Macros Seamlessly in WPS Spreadsheets

WPS Spreadsheets offers a highly compatible and robust environment for managing VBA macros. You can easily edit dynamic range logic, debug code, and execute automated tasks with a built-in macro editor similar to Microsoft Excel.

  1. 1. Open Your Workbook: Launch WPS Spreadsheets and open your macro-enabled workbook (.xlsm).
  2. 2. Access the Developer Tab: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to launch the code editor.
  3. 3. Update and Run Code: Replace the faulty Offset and Resize logic with direct range definitions, save your module, and run the macro.
Fully compatible with Microsoft Excel VBA syntax (.xlsm files)Built-in Macro Editor for quick debugging and code correctionLightweight application that executes automated tasks quicklyFamiliar user interface for seamless workflow migration
microsoft office alternative - wps office

Frequently Asked Questions

Why does the Resize method select one extra column in my VBA macro?

The Resize property is inclusive, meaning it counts the starting column as column 1. If you calculate the width by mathematically subtracting two column indices without adjusting for inclusivity, the resulting range will be one column too wide.

Why should I avoid using Select and Selection in Excel VBA?

Using Select and Selection requires Excel to actively change the user interface, which drastically slows down macro execution. It also increases the risk of runtime errors if the user clicks elsewhere. Defining ranges directly (e.g., Range.Clear instead of Range.Select followed by Selection.Clear) is faster and more stable.

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

You can find the last used row starting from a specific cell and looking downwards using 'Range("A1").End(xlDown).Row', or by looking upwards from the bottom of the sheet using 'Cells(Rows.Count, "A").End(xlUp).Row'.

Does WPS Office support running Excel VBA macros?

Yes, the Pro and Enterprise versions of WPS Office provide comprehensive support for VBA macros. You can open .xlsm files, access a dedicated Visual Basic editor, and run your Excel macro scripts without needing to rewrite them.