VBA & Macro Problems

How to Use Excel VBA Macro to Delete Rows or Cells Containing Blanks

John Wilson Oct 1, 2026 868 views

Question details

The user needs a VBA macro solution to locate empty cells across specific columns and automatically delete those blank cells or their corresponding entire rows.

How to Use an Excel VBA Macro to Delete Rows or Cells Containing Blanks

Product: Microsoft Excel
Device & OS: not provided
Scenario: Cleaning up a dataset where blank cells appear randomly across multiple columns, requiring an automated method to remove them without manually searching.
Observed behavior: Unwanted blank cells interrupt data continuity, making it necessary to programmatically delete them or shift data without messing up the row indices.

Before you start

Before running any VBA macro that deletes data, ensure you have created a complete backup of your workbook, as VBA actions cannot be undone using the standard Undo (Ctrl+Z) function.

Solution 1Recommended

Use a VBA Macro with a Backwards Loop to Delete Blank Rows

The most reliable method to delete rows using VBA is to loop backward from the bottom row up to the top. This prevents row indices from shifting unexpectedly and skipping rows during the deletion process.

When deleting rows or cells in VBA, always loop from the bottom up. If you loop from top to bottom, deleting a row causes the rows below it to shift up. Consequently, the macro will skip checking the row immediately following the deleted one.

By specifying an array of target columns (such as B and D) and defining your worksheet name, you can limit the macro's search scope and securely delete only the relevant blank cells or parent rows.

Open the VBA Editor

Press ALT + F11 on your keyboard to launch the Microsoft Visual Basic for Applications editor.

Insert a New Module

Click on 'Insert' in the top menu bar, then select 'Module' to create a blank workspace for your code.

Write the For-Next Loop Backwards

Set up your loop using 'For i = LastRow To 2 Step -1'. Starting at '2' ensures the macro skips the header row and only evaluates actual data.

Specify the Columns Array

Add an array variable to define which columns to check. For example, use 'ColArray = Array("B", "D")' to only check for blanks in columns B and D.

Execute the Macro

Press F5 or click the 'Run' button in the toolbar to execute the macro. Verify that the blank rows have been successfully deleted from your dataset.

Use a VBA Macro with a Backwards Loop to Delete Blank Rows

Customize the Code: Make sure to update the worksheet name, the array of column letters, and the deletion range in the code to perfectly match your specific workbook structure before running it.

Manage Data Effectively

Easily Delete Blank Rows in WPS Spreadsheet Without VBA

While VBA is powerful, WPS Spreadsheet provides an intuitive, built-in 'Go To Special' tool to instantly find and delete blank cells or rows. This eliminates the need for complex coding and makes data cleanup fast and accessible for all users.

1. Select Your Data Range: Open your workbook in WPS Spreadsheet and use your mouse to highlight the entire dataset containing the blank cells.
2. Open the Go To Dialog: Press Ctrl + G on your keyboard to open the 'Go To' window, then click the 'Special' button.
3. Select Blanks: In the Go To Special dialog box, select the 'Blanks' option and click 'OK'. All empty cells within your selected range will now be highlighted.
4. Delete the Cells or Rows: Right-click on any of the highlighted blank cells and select 'Delete' from the context menu.
5. Choose Deletion Method: Select 'Entire Row' to remove the whole row, or 'Shift cells up' to just remove the blank cells, then click 'OK' to finish.

Fully compatible with Microsoft Excel (.xlsx, .xls) formats.Built-in 'Go To Special' feature to instantly locate all blank cells without VBA.No coding required for standard data cleanup tasks.Lightweight, fast, and completely free to download.

Frequently Asked Questions

Why should I loop backwards when deleting rows with a VBA macro?

Looping backwards (from the bottom up) ensures that when a row is deleted, the rows shifting up do not affect the index sequence of the remaining unchecked rows. If you loop forwards, the macro will likely skip rows immediately following a deleted row.

Can I undo a VBA macro after it deletes cells or rows?

No, actions performed by a VBA macro clear the Undo stack and cannot be reversed using the standard Undo command (Ctrl+Z). Always create a copy or backup of your workbook before running deletion macros.

How do I specify which columns the macro should check for blanks?

You can define the target columns using an array in your VBA code, for example, `Array("B", "D")`. You then nest a loop to iterate through this array so the macro only evaluates cells within those specific columns.

How do I prevent the macro from deleting my header row?

Set the endpoint of your backwards loop to row 2 instead of row 1 (e.g., `For i = LastRow To 2 Step -1`). This ensures the first row, which typically contains your column headers, remains completely untouched.

Excel & Data Problems