Legolas786

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
```

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
ASKER

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 _`

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

ASKER

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.