Clear Contents in multiple cells based on certain criteria

[alang84]

I have data in cells T11:U19. I need to clear contents in the cells in U and adjacent cells in T if a zero is found in U. For example, if U15 contains a zero, I need to clear the contents in cells U15 and T15. I can't delete the cells because I have other data and I don't want the other data to shift. Any help is appreciated.

 

[kevin9999]

One option

Sub Clear_Zeros()
    For Each c In ActiveSheet.Range("T11:T19")
        If c.Value = 0 Then c.Resize(, 2).ClearContents
    Next c
End Sub

 

[alang84]

One option

Sub Clear_Zeros()
    For Each c In ActiveSheet.Range("T11:T19")
        If c.Value = 0 Then c.Resize(, 2).ClearContents
    Next c
End Sub

Hi. The data is column T did not clear out.

 

[kevin9999]

Hi. The data is column T did not clear out.

It did on the test data I threw together. It's impossible to tell why it's not working for you without seeing your actual data. Could you provide a small sample using the XL2BB add in?

 

[kevin9999]

When I run the code, the sheet goes from this:

Book1
	S	T	U	V
10
11		0	data
12		0	data
13		not zero	data
14		not zero	data
15		not zero	data
16		0	data
17		not zero	data
18		0	data
19		0	data
20
Sheet1

Cell Formulas
Range	Formula
T12	T12	=1-1

to this:

Book1
	S	T	U	V
10
11
12
13		not zero	data
14		not zero	data
15		not zero	data
16
17		not zero	data
18
19
20
Sheet1

 

[alang84]

When I run the code, the sheet goes from this:

Book1
	S	T	U	V
10
11		0	data
12		0	data
13		not zero	data
14		not zero	data
15		not zero	data
16		0	data
17		not zero	data
18		0	data
19		0	data
20
Sheet1

Cell Formulas
Range	Formula
T12	T12	=1-1

to this:

Book1
	S	T	U	V
10
11
12
13		not zero	data
14		not zero	data
15		not zero	data
16
17		not zero	data
18
19
20
Sheet1

Hi. The data will be in column t and the 0s will be in column u.

 

[kevin9999]

Hi. The data will be in column t and the 0s will be in column u.

Doh! My sincere apologies - I must learn to read the question more carefully

Try this instead...

Sub Clear_Zeros()
    For Each c In ActiveSheet.Range("U11:U19")
        If c.Value = 0 Then c.Offset(, -1).Resize(, 2).ClearContents
    Next c
End Sub

 

[alang84]

No worries. Thanks for your help.

 

[kevin9999]

No worries. Thanks for your help.

Happy to help and thanks for the patience