Macro to clear Zero

[howard]

I have tried to write a code to clear zeroes hat contain a formula

Sub ClearZero()
With Sheets("Mar-2020")

    If Range("S12:W160").Value = 0 Then
Range("S12:W160").Value = ""
End If
End With
End Sub

Vat.2019.2020.xls
	S	T	U	V	W
17	0.00	0	0.00	0.00	0.00
18	0.00	0	Journal Check	574.57	574.57
19	0.00	0	0.00	0.00	0.00
Mar-2020

Cell Formulas
Range	Formula
S17:W19	S17	=D91

It would be appreciated if someone could assist me in resolving this

 

[Michael M]

Is this what you want ?

Sub ClearZero()
With Sheets("Mar-2020")
ans = Application.WorksheetFunction.Sum(Range("S12:W160"))
If ans = 0 Then
Range("S12:W160").ClearContents
End If
End With
End Sub

 

[howard]

Thanks for the help


The code is not clearing the zeroes -see sample data below

Please check & amend

Macro to Clear Formulas with Zero.xlsm
	S	T	U	V	W
12	BR1	BTR1	112	0	0
13	BR1	BTR2	210	0	0
14	BR1	BTR3	71	0	0
15	BR1	BTR4	262	0	0
16	BR1	BTR5	123	0	0
17	BR1	BTR6	205	0	0
18	0	0	0	0	0
19	0	0	0	0	0
20	0	0	0	0	0
21	0	0	0	0	0
22	0	0	0	0	0
23	0	0	0	0	0
24	0	0	0	0	0
25	0	0	0	0	0
26	0	0	0	0	0
27	0	0	0	0	0
28	0	0	0	0	0
29	0	0	0	0	0
30	0	0	0	0	0
31	0	0	0	0	0
32	0	0	0	0	0
33	0	0	0	0	0
34	0	0	0	0	0
35	0	0	0	0	0
36	0	0	0	0	0
37	0	0	0	0	0
Mar-2020

Cell Formulas
Range	Formula
S12:W37	S12	=A2

 

[Michael M]

This way then

Sub ClearZero()
With Sheets("Mar-2020")
For Each c In Range("S12:W160")
    If c.Value = 0 Then c.Value = ""
Next c
End With
End Sub

 

[howard]

Thanks Michael

Code works perfectly

 

[Michael M]