Solved: How do I say "begin with"

[Amanda1]

Hi,:hi:

In the code
If (.Cells(Lrow, "A").Value) <> "" Then
.Rows(Lrow).Delete


What would be the right way to change this in order to delete every row that does not begin with a number of some description.: pray2:

For example a lot of rows in my workbook begin with a number (they are all formatted as "general") and range from 1 all the way up to perhaps 5000, but not necessarily in numerical order. Mixed in with this lot are rows that just have plain text, dots, dashes, hyphens, spaces, blanks etc etc. I want to delete all those and only keep the ones whose very first character in the row is a number.

I've tried and managed to delete my whole worksheet (don't laugh),:rotlaugh: then I managed to end up with only one row left & that started with 1. I've tried every permutation I can think of and cannot get it right.:banghead:

Please put me out of my misery & tell me what is the correct way?

Cheers

Amanda PS Before anyone shouts at me, I did ask the question at the very end of my previous thread but as the thread is finished now, I am asking the question here so that people can see it.:winking2:

[tkaplan]

try isnumeric(left(A1,1)= false as your condition to delete. this checks if the most left character in cell a is a number.

[Tommy]

Hi Amanda,

The Val function returns a value if it is numeric otherwise it returns 0. As long as there does not need to be a consideration of val("4 5 6 Messy Text") this will return 456 not 4, Val("4 Text") will return 4, Val("Text") will return 0.


If Val((.Cells(Lrow, "A").Value)) <= 0 Then
.Rows(Lrow).Delete


HTH

[Amanda1]

:huh:

I'm lost.

(You know this morning I was feeling very good at having managed to compile a script without help - I was obviously hoisted by my own petard, or suffering a relapse from this VB bug - not sure which, cause you have now just made me feel like a right clot).
:blush :whyme:


Baby steps for me, (no never mind the baby steps - just a crawl will do) - I can understand the logic of your suggestion, but how and where does it get incorporated into the code? :dunno

Cheers

Amanda

[Amanda1]

Hi Tommy

You posted one, while I was busy typing a response to tkaplan, so I have just seen your explanation and example. Thanks I'm going to do some looking up on that one -it seems a very nice function & works brilliantly.

Thanks (The smilie box won't open for me - I think I have exhausted it).

Cheers & take care

Amanda

[Tommy]

tkaplan's code was ment to be placed in the if statement. I changed it from = False to Not not because it is wrong, just my preference. :) It basically does the same as the val function.

If Not IsNumeric(Left(.Cells(Lrow, "A").Value)) Then
.Rows(Lrow).Delete

[mvidas]

If you're looking for it to begin with a number (even if it were 0), you could use the Like operator with the # and * wildcards:If .Cells(LRow, "A").Value Like "#*" ThenThough IsNumeric does the same thing (as does the val, unless the cell contains something like "0 records", which maybe you wouldnt want that returned anyways?)

[Bob Phillips]

Guessing a bit after my last attempt


If Not IsNumeric(Left(.Cells(Lrow, "E").Value, 1)) Or _
Left(.Cells(Lrow, "E").Value, 1) = "0" Then
.Rows(Lrow).Delete

[Amanda1]

More lessons learnt today - thanks everyone!!!!!!!:grouphug: :joy: :yay

[Aussiebear]

So... for those of us more easily confused. Which suggestion has won the tip of the day?

[Amanda1]

Now that would be telling!:winking2:

(I'm not risking upsetting the brains that help me)

[Aussiebear]

Okay, I'll just retreat to my corner of the round room then....

[Bob Phillips]

Now that would be telling!:winking2:

(I'm not risking upsetting the brains that help me)

Then in true paranoid fashion, I am going to assume it wasn't mine, and therefore refuse to help you again http://vbaexpress.com/forum/images/smilies/whyme.gif

[Amanda1]

OK, OK, OK, OK (put in a loop or however else you tell a computer to keep repeating what it is doing).

No paranoia please, and as for people going round the "bend", my conscience couldn't stand it.

I used all of them - Hows that?

Cheers

Amanda

[Aussiebear]

I used all of them - Hows that?

Each one of an individual day? Now that is clever....:beerchug:

Ted

[Amanda1]

I work fast

[Aussiebear]

So it seems... to us mostly confused souls.