Vba Loop to delete rows with numeric value in Cell

Question

I want to delete any row with a numeric value in a cell. When I run the following code it correctly evaluates the IsEmpty and IsNumeric() functions and deletes the row. However it stops for some reason and will complete after 3 tries. Note: I've tried Cell.Value and Cell.Value2.

For Each Cell In Range("H1:H600")
    If Not IsEmpty(Cell) Then
        If IsNumeric(Cell.Value) Then
            Cell.EntireRow.Delete
        End If
    End If
Next

when deleting go from the bottom up, you'll get much more consistent results. — sous2817
– sous2817, Commented Aug 4, 2016 at 19:25
that's because when you delete a row, the other rows get bumped up. If you delete 1 reduce i by i=i-1 — David G
– David G, Commented Aug 4, 2016 at 19:26
You need to loop backwards or it will skip lines. See here for three options; stackoverflow.com/questions/33744149/… — Scott Craner
– Scott Craner, Commented Aug 4, 2016 at 19:27
Possible duplicate of Removing rows without skipping a row how to? — user4691433
– user4691433, Commented Aug 4, 2016 at 19:33

Tim Williams · Accepted Answer · 2016-08-04 19:36:46Z

1

If you don't have formulas in that range:

Range("H1:H600").SpecialCells(xlCellTypeConstants, xlNumbers).EntireRow.Delete

answered Aug 4, 2016 at 19:36

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Slai Over a year ago

I was thinking the same but some of his numbers might be stored as text

Tim Williams Over a year ago

That will also remove any numbers formatted as text

user2336288 Over a year ago

Apologies for the late reply. This worked and is obviously a lot fast than what I had. Thanks

Collectives™ on Stack Overflow

Vba Loop to delete rows with numeric value in Cell

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related