Use an Excel VBA Macro to Clear Zero Values in Cells
Question details
Clear cells containing numeric zero values in columns D through Z, starting at row 2, using an Excel VBA macro.

- 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.
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.
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.
In desktop Excel, open the workbook and press Alt+F11 to open the Visual Basic Editor.
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
In the code, change 150 in Range("D2:Z150") to the last row you want to include. Row 1 is excluded.
Return to Excel, select the worksheet to clean, and press Alt+F8. Select ClearZeroValues and click Run.
To keep the VBA code in the workbook, choose File > Save As and select Excel Macro-Enabled Workbook (.xlsm).

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.

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.




