VBA & Macro Problems

How to Delete Specific Columns in Excel VBA Without Deleting Rows

Guest Writer Oct 12, 2026 869 views

Question details

The user wants to delete specific cells (Columns A through AA) where Column A is blank using VBA, without using the EntireRow property so that objects (like text boxes in Column AB) aren't moved or disrupted.

How to Delete Specific Columns in Excel VBA Without Deleting Entire Rows

Product: Excel
Device & OS: not provided
Scenario: Deleting conditionally blank cells in a restricted range using a macro without affecting layout elements placed outside the target range.
Observed behavior: Using EntireRow.Delete unintentionally shifts or disrupts objects like text boxes located beyond the target range (e.g., in column AB).

Before you start

Before running VBA macros that delete cells, it is highly recommended to save a backup copy of your workbook, as macro actions cannot typically be undone using the standard Undo feature.

Solution 1Recommended

Use Filtered Range and Delete with Shift Up

Avoid EntireRow.Delete by isolating the specific range (A:AA) and deleting only the visible cells shifted up.

When you use 'EntireRow.Delete', Excel removes the whole horizontal span of the worksheet, which shifts any shapes, charts, or text boxes located further to the right.

By defining the precise range (Columns A to AA) and applying 'Shift:=xlUp', you restrict the deletion to just those specific cells, keeping the rest of the worksheet intact.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications Editor, and insert a new Module.

Define the target range

Set your VBA range to strictly target the specific columns (e.g., Set rng = Range("A3:AA" & lastRow)).

Apply AutoFilter

Use the AutoFilter method on Field 1 to target the blank cells with Criteria1:="=".

Select visible cells

Use the SpecialCells(xlCellTypeVisible) method to isolate only the filtered, visible cells in your specified range.

Delete and shift up

Execute 'rng.Delete Shift:=xlUp' rather than using 'EntireRow.Delete' to remove the range without affecting objects in column AB and beyond.

Use Filtered Range and Delete with Shift Up

VBA Code Example: Set rng = Range("A3:AA" & lastRow) rng.AutoFilter Field:=1, Criteria1:="=" Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1).SpecialCells(xlCellTypeVisible) If Not rng Is Nothing Then rng.Delete Shift:=xlUp

Advanced Macro Support in WPS Office

Use WPS Spreadsheet to Write and Execute VBA Macros

WPS Office provides robust support for VBA macros, allowing you to automate tasks, apply AutoFilters, and manage cell ranges exactly as you would in Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook.
2. Access the VBA Editor: Go to the 'Developer' tab on the ribbon and click 'Visual Basic' or simply press Alt + F11.
3. Insert the macro: Insert a new module and paste your rng.Delete Shift:=xlUp macro code.
4. Run the code: Run the macro to securely delete columns A to AA without moving text boxes outside the range.

Fully compatible with Microsoft Excel VBA syntax and scriptsEasily manage object placements and cell ranges without layout disruptionLightweight application with high performance for large datasetsFamiliar UI that requires zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why does EntireRow.Delete move my text boxes?

EntireRow.Delete removes the entire horizontal row across all columns (A to XFD). Any objects, like text boxes or charts anchored to cells anywhere within that row, will be shifted or deleted along with it.

How do I enable the Developer tab to run VBA?

Go to File > Options > Customize Ribbon, and check the box for the 'Developer' tab. This allows you to access the Visual Basic Editor and manage your macros.

Can I undo a VBA macro execution?

No, standard macro executions clear the undo stack in the application. Always save a backup of your workbook before testing or running a cell-deleting macro.

What does xlCellTypeVisible do in the VBA code?

The SpecialCells(xlCellTypeVisible) function ensures that only the cells currently visible after applying the AutoFilter (in this case, the blank ones) are selected for deletion, preventing hidden rows from being accidentally removed.

Excel & Data Problems