Deleting specific text using VBA ?
 

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?

Deleting specific text using VBA ?
 

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

--
Gary''s Student - gsnu200774


"Confused" wrote:

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?

Deleting specific text using VBA ?
 

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?

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200774


"Confused" wrote:

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?

Deleting specific text using VBA ?
 

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.
"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200774


"Confused" wrote:

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?

Deleting specific text using VBA ?
 

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
--
Gary''s Student - gsnu200774


"Confused" wrote:

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.
"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200774


"Confused" wrote:

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?