VBA & Macro Problems

Use an Excel VBA Macro to Clear Zero Values in Cells

Aamir Naveed AkramAamir Naveed Akram Nov 20, 2026 869 views

Question details

Clear cells containing numeric zero values in columns D through Z, starting at row 2, using an Excel VBA macro.

Use an Excel VBA Macro to Clear Zero Values in Cells
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user is cleaning a worksheet and wants to remove zero values from a specified range.
Observed behavior
Cells containing numeric zero are cleared, while other values remain in place.
Before you start

Open the workbook in a desktop version of Excel that supports VBA, and identify the last row you want to clean. Test the macro on a copy first: it clears formulas that currently return zero as well as typed zeros.

Solution 1Recommended

Run a VBA macro to clear numeric zeros in columns D through Z

Use this macro to clear numeric zeros from a defined range without clearing text values or error cells.

1
Open the VBA editor

In desktop Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor.

2
Insert the macro

Choose Insert > Module and paste this code: Sub ClearZeroValues() Dim cell As Range Dim v As Variant For Each cell In ActiveSheet.Range("D2:Z150") v = cell.Value2 If Not IsError(v) Then If Not IsEmpty(v) Then If VarType(v) <> vbString And IsNumeric(v) Then If v = 0 Then cell.ClearContents End If End If End If Next cell End Sub

3
Set the final row

In the code, change 150 in Range("D2:Z150") to the last row you want to include. Row 1 is excluded.

4
Run the macro

Return to Excel, select the worksheet to clean, and press Alt+F8. Select ClearZeroValues and click Run.

5
Save the macro if needed

To keep the VBA code in the workbook, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm).

Run a VBA macro to clear numeric zeros in columns D through Z
Check formula cells: ClearContents removes a cell's formula if its current result is numeric zero. It leaves cell formatting intact, and the macro does not clear the text value "0".
Free Microsoft Office alternative

Clean and review spreadsheets with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface for opening and editing Excel files. It can help you migrate everyday workbook tasks, though it does not fix Excel-specific VBA issues and macro support can vary.

Review and clean zero values in spreadsheet cellsOpen and edit Microsoft Excel XLSX filesWork with a familiar spreadsheet interfaceUse a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How do I include more than 150 rows?

In the VBA editor, change D2:Z150 in the Range statement to your desired range, such as D2:Z1000, before running the macro.

Will the macro remove formulas that return zero?

Yes. The macro checks each cell's current value, so ClearContents also removes a formula when its result is numeric zero.

Does the macro clear cells containing the text "0"?

No. The macro excludes text values, so a cell containing the text "0" remains unchanged.

Can I run this macro in Excel for the web?

No. Open the workbook in a desktop version of Excel that supports VBA to edit and run the macro.

Why should I save the workbook as an XLSM file?

The XLSM format can retain VBA code. Use File > Save As and select Excel Macro-Enabled Workbook if you want to keep the macro in the file.