Finding Alpha Numeric in string and delete row

[Chris_AAA]

Hi All


I have a worksheet, in which 'generally' column a will be numeric codes only. on the rare ocassion, column A will be mixed with alpha numeric data.

Im after some advice in which the code searches all of column, A, if alpha numeric, then delete whole row, and loop through till end. if no alpha numeric codes, then continue with rest of code.

Any help is greatly appreciated.

Thank you

Am sure its an easy solution, but its baffled me.

[Paul_Hossler]

I think the logic would be something like this

Sub phh_1()
    On Error Resume Next
    ActiveSheet.Cells(1, 1).CurrentRegion.Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).EntireRow.Delete
    ActiveSheet.Cells(1, 1).CurrentRegion.Columns(1).SpecialCells(xlCellTypeFormulas, xlTextValues).EntireRow.Delete
    On Error GoTo 0
End Sub

If you had a 'numeric string' like "12341234" this will delete is also

If that's possible, there's a more complicated way to do it

[Chris_AAA]

Hi Paul, thanks for the reply buddy.

So would the above identify and delete a row, if column A:A for instance had 12345a678 or 98648L54454? that is essentially the task in hand. I was trying to incorporate something like the below i found of the internet into a code...

=AND(SUMPRODUCT(--ISNUMBER(--MID(A2,ROW($1:$999),1)))<LEN(A2),MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))<=LEN(A2))

[Paul_Hossler]

12345a678 or 98648L54454? that

Easy for you to test, but I think both of those would be treated as strings and their row deleted

[Chris_AAA]

yeah good shout... I did test, and works a dream.

Thank you kindly for your wise words :-)