How to delete rows that start with a letter rather than a number

[chris2013]

Hi I have a sheet that has letters and numbers in the "A" column but I want to keep only those that start with a number and delete all those that start with a letter here is the code that I adapted from another post :


count = 0
For i = 7 To 151
Select Case Val(Left(Range("A" & i).value, 1))
Case 3
'do nothing
Case Else
Rows(i).Delete
count = count + 1
End Select
Next i




It leaves several columns that start with something other then a 3 at the bottom.

Help anyone?

 

[VoG]

Try looping backwards

For i = 151 To 7 Step -1

 

[Jonmo1]

Welcome to the board..

When deleting rows or columns, you have to go backwards (top to bottom / right to left)

Example, if you get to say row 10, and it meets the criteria and is deleted.
So what was in row 11 is now in row 10.
And what was in row 12 is now in row 11
Your loop then moves on to test row 11.
So whatever was in row 11 essentially got skipped.


Try changing
For i = 7 To 151
to
For i = 151 To 7 Step -1

 

[chris2013]

Thanks for the quick reply!
you guys are awesome

 

[Jonmo1]

Glad to help, thanks for the feedback.