Deleting specific text using VBA ?

C

Confused

I have a spreadsheet that contains over 6,000 lines and 20 columns. The
spreadsheet changes often. Each of these columns may have "0", "-", "0.00%",
"call desk" etc. Instead of manually recording the macros to delete these
specific values for each column, is there a code to do this?
 
G

Gary''s Student

Adapt this small macro to your needs:

Sub cleanup()
s = Array("0", "-", "0.00%", "call desk")
For Each r In ActiveSheet.UsedRange
v = r.Text
For i = 0 To 3
If v = s(i) Then
r.Clear
End If
Next
Next
End Sub
 
C

Confused

Thanks. This works great.
What if the "-" is actually a "0" but shows up as a "-"? what is the
correct way to show that in the array?
 
C

Confused

For my own educational purposes, can you explain what s, v, r, and i mean?
also, what is the purpose of the line "For i = 0 To 3"?

Thanks.
 
G

Gary''s Student

s is an array variable - just a list of values
v is the value of the data in the cell being examined
r is a range variable - it represents the cells being examined
i is a loop variable - we are repeating everything between For and Next 4
times
 
Top