asked on # How to delete cells that begin with using VBA?

Hi,

Is it possible in VBA to delete all the cells that begin with a certain letter/word/number? I use the following but it works more like if the whole cell contains the values, another problem I have is that if i have a cell that begins with a date for example "12/12/2014 text" the below code would not work.

Is it possible in VBA to delete all the cells that begin with a certain letter/word/number? I use the following but it works more like if the whole cell contains the values, another problem I have is that if i have a cell that begins with a date for example "12/12/2014 text" the below code would not work.

```
Set rngH = wst.Range("H1:H" & wst.Range("H65536").End(xlUp).Row)
For Each rng In rngH
bnlFoundInvalid = False
bnlFoundValid = False
For intCounter = 0 To 12
If InStr(1, rng.Offset(0, intCounter).Value, "Set", vbTextCompare) > 0 Or _
InStr(1, rng.Offset(0, intCounter).Value, "New", vbTextCompare) > 0 Or _
InStr(1, rng.Offset(0, intCounter).Value, "1", vbTextCompare) > 0 Or _
IsNumeric(rng.Offset(0, intCounter).Value) Then
If bnlFoundInvalid = False Then
'An invalid column.
intColInValid = intCounter
End If
bnlFoundInvalid = True
rng.Offset(0, intCounter).ClearContents
Else
'1st valid column.
If bnlFoundValid = False Then
intColValid = intCounter
End If
bnlFoundValid = True
End If
If bnlFoundInvalid = True Then
If bnlFoundValid = True Then
If intColValid > 0 Then
'Col H had an invalid value.
rng.Value = rng.Offset(0, intColValid).Value
End If
End If
End If
Next intCounter
Next rng
```

Visual Basic Classic

Hi,

pls try

pls try

```
If Rng.Offset(0, intCounter).Value Like "Set*" Or _
Rng.Offset(0, intCounter).Value Like "New*" Or _
(Rng.Offset(0, intCounter).Value Like "1*" And _
Not Rng.Offset(0, intCounter).Value Like "##/##/####*") Or _
IsNumeric(Rng.Offset(0, intCounter).Value) Then
```

Regards
Rgonzo1971

Hi,

i ahve tried your coding but it doesnt seem to clear the cell, for example if a cell contains "the colour is red", in your code i have the following and it does not clear it?

also some times there might be a space before the word so for example " the colour is red" and i have tried

but again it does not work?

Phillip Burton

can you please provide the full line? as basing with what you have said the following wont work?

Hi,

i ahve tried your coding but it doesnt seem to clear the cell, for example if a cell contains "the colour is red", in your code i have the following and it does not clear it?

```
rng.Offset(0, intCounter).Value Like "the*" Or _
```

also some times there might be a space before the word so for example " the colour is red" and i have tried

```
rng.Offset(0, intCounter).Value Like " the*" Or _
```

but again it does not work?

Phillip Burton

can you please provide the full line? as basing with what you have said the following wont work?

```
If InStr(1, left(rng.Offset(0, intCounter).Value,len("Set")), "Set", vbTextCompare) > 0 Or _
```

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question
I think both of our suggestions will work - I think you need to post your spreadsheet.

Sorry it was my mistake, i was searching the wrong range - schoolboy error. thanks guys!

rng.Offset(0, intCounter).Value

in line 7 to

left(rng.Offset(0, intCounter).Value,len("Set

if you are looking for a general approach.