VBA & Macro Problems

How to Fix VBA Error 424 Object Required in a With Statement

Emma Brown Sep 28, 2026 869 views

Question details

The user is encountering a VBA Error 424 when attempting to delete columns based on header values within a With statement.

How to Fix VBA Error 424 (Object Required) in a With Statement

Product: Spreadsheet
Device & OS: not provided
Scenario: Running a VBA macro that opens a workbook, assigns a worksheet to a variable, and loops through columns to delete specific ones.
Observed behavior: The macro throws a run-time Error 424 at the Select Case .Offset(0, i) line because the reference object is destroyed when its column is deleted.

Before you start

Before editing your VBA code, ensure you have saved a backup copy of your macro-enabled workbook (.xlsm) to prevent any unintended data loss during testing.

Solution 1Recommended

Loop Backward When Deleting Columns

Reverse the direction of your loop so that deleting a column does not shift the index or destroy the object references for the remaining columns.

When you loop forward (e.g., from Column A to Z) and delete a column, all subsequent columns shift to the left. If your 'With' statement relies on a specific cell reference in the deleted column, VBA loses track of the object, triggering Error 424.

By looping backward (from right to left), any deleted columns only affect the space you have already processed, keeping your current object references intact.

Open the VBA Editor

Press Alt + F11 on your keyboard or navigate to the Developer tab and click on 'Visual Basic' to open the macro code.

Locate the column deletion loop

Find the For loop in your script that iterates through the columns, which likely looks something like 'For i = 1 To LastCol'.

Change the loop direction

Modify the loop to step backward by starting from the last column and ending at the first. Add the 'Step -1' instruction at the end, for example: 'For i = LastCol To 1 Step -1'.

Adjust the With statement reference

Ensure that the object declared in your With statement (like bankSh.Cells) does not reside in a column that gets deleted during the loop.

Best Practice: Always use a backward loop (Step -1) as a standard practice whenever you write macros designed to delete rows or columns.

Edit and Run Macros Seamlessly in WPS Spreadsheet

WPS Office provides robust compatibility with Excel macros and VBA. You can effortlessly edit your code, troubleshoot errors like 'Object Required', and run your automated tasks smoothly.

1. Open your file: Open your macro-enabled workbook directly in WPS Spreadsheet.
2. Access developer tools: Navigate to the Developer tab located on the top ribbon.
3. Open the VBA editor: Click on the 'Visual Basic' icon or press Alt + F11 to launch the editor.
4. Apply fixes and run: Update your code with the backward looping fix and click the 'Run' button to test your macro.

Fully compatible with Microsoft Excel .xlsm and .xlsb formats.Built-in Developer tools for efficient macro editing and debugging.Lightweight and fast performance for handling complex datasets and scripts.

microsoft office alternative - wps office

Frequently Asked Questions

What does Run-time error 424 'Object Required' mean in VBA?

This error occurs when VBA expects a valid object (such as a Range, Worksheet, or Workbook) to execute a command, but the object is either not properly set, misspelled, or has been deleted from the document during runtime.

Why does my macro work on the second run but fail on the first?

This usually happens because the initial run deletes a required reference column, causing the crash. On the second run, the problematic column is already gone, so the code skips the deletion and proceeds without encountering the missing object error.

How do I write a backward loop in VBA?

You can write a backward loop using the For statement combined with Step -1. For example, 'For i = 10 To 1 Step -1' tells the program to start at 10 and count down to 1.

Excel & Data Problems