Excel VBA Delete rows that do not contain a number in the string

[dsmuhammad]

Hello,

I have a string of data in a spreadsheet which only appears in column A of my spreadsheet. I would like a VBA Excel code to delete all rows that do not contain a number in the row. I thought that this would be easy, but I am having trouble finding the correct code to delete the cells which do not contain a number. My string is alphanumeric.

17320FAB9 CMLTI 12-A B 8.3/8.3 5 11 10 6/24
17320FAE3 CMLTI 12-A C 2.1/2.1 0 0 10 6/24
* Bids accepted for either size.
My name is Tom
I
Henry
02660TDH3 AHM 05-1 6A 1.9/0.4 L+200 14.3 15 5/25 L90s
126671RM0 CWL 02-3 1A1 0.6/0.0 L+37 54.9 15 HEQ
07386HKN0 BALTA 04-7 M 0.4/0.2 2.6 9.9 13 5/25
45660HAA6 INDX 06-AR21 A1 0.6/0.2 L+12 0 31 5/25

I would like to delete the lines that do not have a number in it. This data can vary on a daily basis, thus the code cannot be specific to what I have posted here. If there is someone that can help me out, that would be great.

[rcm]

Please Login or Register  to view this content.

[Trebor76]

Hi dsmuhammad,

Welcome to the forum!!

Try this (initially on a copy of your data as the results cannot be undone if they're not as expected) which as it doesn't loop is very fast:

Please Login or Register  to view this content.

I've also written an article about different ways to delete rows on my website which explains my code here.

Regards,

Robert

[dsmuhammad]

Robert,

Thank you, after testing this macro, it seems as though, it is doing the job. I really appreciate this. There is only one slight thing that I would like to point out. I had a one line that said PRIME/ALTA, this pretty much can come to us on a daily basis, so I put in a code to get rid of that line so that is solved. I was just curious as to why that line would not be deleted since it does not contain a number.

This is not as pressing of a question to answer since it deleted all of the other rows that did not contain a number. The reason why I am also asking this question is just in case something else like this came up and I needed it for future macros.

I really am grateful for this code that you already gave me because I can move on to the next assignment.

[jindon]

I had a one line that said PRIME/ALTA.....

This should do

Please Login or Register  to view this content.

[Trebor76]

I had a one line that said PRIME/ALTA, this pretty much can come to us on a daily basis, so I put in a code to get rid of that line so that is solved. I was just curious as to why that line would not be deleted since it does not contain a number

Not sure as it did for me

[rcm]

Please Login or Register  to view this content.

The "/" has a code value of 47 and the range of characters in [0-9] has code between 48 and 57. I guess it "spilled" over

Nevertheless, explicity did the job

[jindon]

What's this pattern?

Please Login or Register  to view this content.

Huh?

That's equivalent to

Please Login or Register  to view this content.

Why include comma????

[rcm]

it should but as I tested it, it also removed de line with "/" so a pattern is not, it is only the explicit characters that are to be seeked after.

[jindon]

Tested?

How?

Your pattern will not delete the data like "ABC,DEF".
Too risky for the OP to post a code as a solution when you don't really know how it works.

[rcm]

thanks for the advice but I did test it with the patterns in post #1. I'll be more carefull next time ...