Removing rows containing Alpha characters in column B

[kayakindude]

Hello,

The following code (credit Tushar Mehta) works great for deleting a row if a cell in column B contains and alpha character at the beginning or end, however it is not deleting if one or more alpha characters are mixed in- example 123B456H87

How could the code be adjusted to delete based on finding an alpha character anywhere in the cell?

Please Login or Register  to view this content.

[TMS]

Can't see it would make a difference where the alpha characters are … it wouldn’t be numeric, full stop.

A B C D E F
123B456H87 FALSE =ISNUMBER(B2) TRUE =ISTEXT(B2)
A12345 FALSE =ISNUMBER(B3) TRUE =ISTEXT(B3)
56789B FALSE =ISNUMBER(B4) TRUE =ISTEXT(B4)
12345 TRUE =ISNUMBER(B5) FALSE =ISTEXT(B5)

[kayakindude]

I am not a skilled user, but agree did not see anything in the code that would only delete leading/trailing. Attached a test book, tripled the sample set and that resulted in the same alpha-numeric cell failing to delete.

[TMS]

OK, there is clearly a difference between VBA IsNumeric and woksheet ISNUMBER

IsNumber checks if a value is stored as a number. Whereas, IsNumeric checks if a value can be converted into a number.

For example, if you pass a blank cell as a parameter, IsNumber will return FALSE, while IsNumeric will return TRUE. Also, if you pass a cell containing number stored as a text, IsNumber will return FALSE and IsNumeric TRUE.

Quote courtesy of: https://www.automateexcel.com/vba/is...0return%20TRUE.

That said, I can't understand what is happening here.

I have added some Debug.Print statements to monitor the loop. I have also replaced IsNumeric (Alpha) with Application.WorksheetFunction.IsNumber (Beta_TMS).

The latter version works as expected ...

Please Login or Register  to view this content.

[FONT=Courier New] 
Alpha 
processing row:              10           24943E        True 
deleting row:                10           24943E 
processing row:              9            E20097136     True 
deleting row:                9            E20097136 
processing row:              8            20554D8       False 
processing row:              7            24943E        True 
deleting row:                7            24943E 
processing row:              6            E20097136     True 
deleting row:                6            E20097136 
processing row:              5            20554D8       False 
processing row:              4            24943E        True 
deleting row:                4            24943E 
processing row:              3            E20097136     True 
deleting row:                3            E20097136 
processing row:              2            20554D8       False 
Beta 
processing row:              4            20554D8       True 
deleting row:                4            20554D8 
processing row:              3            20554D8       True 
deleting row:                3            20554D8 
processing row:              2            20554D8       True 
deleting row:                2            20554D8 
[/FONT]  

[jindon]

kayakindude,

IsNumeric function has a known bug.
You can see all those returns True

Please Login or Register  to view this content.

1) Loop

Please Login or Register  to view this content.

2) no loop

Please Login or Register  to view this content.

[kayakindude]

Thank you for the feedback and examples, went with the loop sample, which gave me fits until I figured out it needed to be in it's own module and called by the main macro. Thanks again!

[TMS]

Who are you thanking? And which loop sample are you referring to?