PDA

View Full Version : Finding Alpha Numeric in string and delete row



Chris_AAA
07-15-2015, 03:09 AM
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
07-15-2015, 05:48 AM
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
07-15-2015, 05:58 AM
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
07-15-2015, 12:57 PM
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
07-16-2015, 01:10 AM
yeah good shout... I did test, and works a dream.

Thank you kindly for your wise words :-)