VBA & Macro Problems

Delete Specific Cells Based on Blank Criteria Using Excel VBA

Tauseeq Magsi Oct 10, 2026 868 views

Question details

The user wants to delete cells in a specific range (Columns D to H) only when the corresponding cell in a target column (Column G) is blank.

How to Delete Cells in Columns D to H When Column G is Blank Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating spreadsheet data cleanup where only a specific part of a row needs to be deleted and shifted up based on a blank cell in a reference column, without deleting the entire row.
Observed behavior: The goal is to efficiently delete only the intersecting cells in columns D:H and shift them up, preserving data outside this range and avoiding slow row-by-row loops.

Before you start

Before running VBA macros that delete data, ensure you save a backup copy of your workbook because macro deletions cannot be easily undone using standard undo commands.

Solution 1Recommended

Use the Intersect and SpecialCells Method

The most efficient way to delete specific cell ranges based on blanks without looping through thousands of rows.

Using SpecialCells(xlCellTypeBlanks) quickly identifies all blank cells in your target column. By combining this with Intersect, you restrict the deletion area strictly to your desired columns.

This approach prevents accidental data loss in other parts of the sheet and is significantly faster than writing a VBA For loop to check each row individually.

Open the VBA Editor

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

Insert a new module

From the top menu bar, click Insert > Module to create a blank script window where you can write your macro.

Add the VBA code

Paste the following syntax: Intersect(Range("G2:G10000").SpecialCells(xlCellTypeBlanks).EntireRow, Range("D2:H10000")).Delete Shift:=xlShiftUp

Adjust ranges and run

Ensure the row ranges (like 10000) cover your actual dataset. Press F5 or click the Run button to execute the code. Cells in D:H where G is blank will be deleted and shifted up.

Error Handling Tip: If there are no blank cells in column G, the macro will return an error. You can prevent this by adding 'On Error Resume Next' before the deletion line.

Advanced Macro Support in WPS Office

Automate Your Data Cleanup with WPS Spreadsheet Macros

WPS Office fully supports VBA macros, allowing you to run scripts like Intersect and SpecialCells flawlessly. You can easily automate complex data deletion and formatting tasks using the built-in macro editor without performance drops.

1. Download and Install: Download WPS Office from the official website and open your data-heavy spreadsheet.
2. Enable Macros: Navigate to the 'Developer' tab and ensure macros are enabled for your current workbook.
3. Open the VBA Editor: Click on 'Visual Basic' or press Alt + F11 to access the code editor directly.
4. Paste and Execute: Insert a module, paste your Intersect deletion code, and click Run to instantly clean your specified cell ranges.

Fully compatible with Microsoft Excel VBA syntax and .xlsm filesExecutes complex data cleanup tasks rapidly without freezingLightweight installation with a familiar user interface for macro editing

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use a For loop to delete blank rows in VBA?

A For loop processes one row at a time, which can be very slow and resource-heavy for datasets with thousands of rows. The SpecialCells and Intersect method processes all target cells simultaneously, vastly improving execution speed.

Can I change the target columns in the provided code?

Yes, you can easily modify the Range("D2:H10000") part of the VBA code to reflect the specific columns and rows you want to delete data from.

Will this macro delete the entire row if column G is blank?

No, the Intersect function specifically limits the deletion to the intersection of the blank rows and columns D through H. Data in columns A, B, C, I, and beyond will remain entirely unaffected.

What happens if there are no blank cells in the target column?

If no blank cells exist, the SpecialCells(xlCellTypeBlanks) method will throw a runtime error. You can bypass this by adding 'On Error Resume Next' before the command and 'On Error GoTo 0' immediately after it.

Excel & Data Problems