Macro help

[TS | Thomas]

Hey. I'm trying to get a Macro to delete a row in Column A "if" it includes the number 9xxxxxx, e.g. 9000058. I also currently have it set to delete any rows where Column A is blank, or Column A is qqqqqqqq. These 2 work just fine, but I can't seem to get the 9xxxxxx deletion to work. Does anyone know how to get the Macro to look for wildcards? Here's the relevent section of the macro I'm using;

Dim rng As Range, cell As Range, del As Range
Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
For Each cell In rng
If (cell.Value) = "" _
Or (cell.Value) = "9******" _
Or (cell.Value) = "qqqqqqqq" Then
If del Is Nothing Then
Set del = cell
Else: Set del = Union(del, cell)
End If
End If
Next cell
On Error Resume Next
del.EntireRow.Delete

 

[Mictlantecuhtli]

I'm not sure which language you're using, but if cell.Value is a string, you could try with a function that gets the leftmost character(s) from a string.

 

[Nodsu]

Yes. Try testing for cell.Value[0]="9" or however you reference characters in strings in VB

 

[TS | Thomas]

Thanks, I don't really know how to do that yto be honest. I'm basically reverse engineering stuff I've found on google But I have made more progress in another area at least so this is really all I have to figure out. It's possible that there could well be other numbers beginning with 9, e.g. 990057 that wouldn't need to be deleted though so I can't just blanket delete anything that starts with 9. It's pretty minor though really.

 

[Nodsu]

If you know what sort of numbers you don't want deleted then you can just add another test before the deletion test. Like:

if (cell.value.length()=7) and (cell.value[0]) then

to make sure you delete only 7 digit numbers beginning with 9

 

[TS | Thomas]

Nope, I just get compile errors trying to use those. The value must always be 7 numbers long, with 9 being the first number. I'm googling away at it now

 

[Nodsu]

I never said these lines are syntactically correct
Just trying to give you the general idea here.

 

[TS | Thomas]

Nope, I need to be spoon fed the answer here