How can you write a VBA script to delete all cells in a range containing a certain string?

Question

If I have a large range of cells, and I want to delete all cells containing just a "," or similar, how can I express this in code? I was thinking something along the lines of

If Range("C1:C100").Value = "," Then 
Active.Cell.Delete Shift:=xlRight

I know this is terrible, but hopefully it gets my point across.

Thanks!

What happens when you try to run the code in your sample? Any error messages? You learn faster by doing it yourself and then asking questions about the problems you run into. :) — Olle Sjögren, Sep 02 '15 at 09:47
Thanks for the advice. I'm going to test telyln's answer a little bit and try figure out myself what's going on. I'll get back if I'm still stuck — user1996971, Sep 02 '15 at 10:58

score 3 · Accepted Answer · answered Sep 02 '15 at 09:53

3

conceptually (not in front of Excel right now)

dim cel as range
for each cel in Range("C1:C100")
   if cel.Value = "," Then
      cel.Delete Shift:=xlRight 
   end if
next cel

answered Sep 02 '15 at 09:53

teylyn

34,374
4
53
73

Excellent. This worked. I just had to change the Delete line to `Delete Shift:=xlToLeft` Thanks a million! – user1996971 Sep 02 '15 at 11:02

How can you write a VBA script to delete all cells in a range containing a certain string?

1 Answers1