VBA how to delete rows when a number is in a cell

[cazdealer]

Hi,
I want to find a way to create a VBA procedure that will delete all rows that have a number or part of a number in the string in column A.

I have data from A1 to A1000.

For exemple, if this is in the first cell of the row (A), I want the full row to be deleted:
ex: AAA1, 100.93, 10, 1A1, fedex1, 123, q0q


How could I go that?

 

[Weaver]

So you're saying if the first cell contains a digit (0-9), then delete the row?

Do you have headers in row 1?

 

[cazdealer]

Yes exactly, if this is a digit from 0 to 9 in cell A (even if it's combine with letters), I want that full row to be deleted...

no, there is no headers

it starts at A1

thanks

 

[Weaver]

try this (on a copy of your data first...)

Sub deleteRowIfDigit()
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d"
        For r = lr To 1 Step -1
            If .test(Cells(r, "A")) Then Rows(r).EntireRow.Delete
        Next
    End With
End Sub

 

[cazdealer]

Perfect! works like a charm!

one question: what is the .Pattern = "\d" does?
first time I see something like that?

 

[Weaver]

Perfect! works like a charm!

one question: what is the .Pattern = "\d" does?
first time I see something like that?

It means "look for digits"

If you're interested in finding out more, enter "regular expressions" in your favourite search engine (unlikely to be B*ng, apparently...) or follow this link

http://www.aivosto.com/regexpr/help/tutorial.html