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.