Macro not fully running

[grabrail]

I have a bit of a strange one.

I have a workbook, that is a file downloaded from the internet, from one of my suppliers. I then have a couple of Macros I run to manipulate the file into the format I want.

Once everything is formatted, I am left with a number of rows which are erroneous that I dont need, so I have another macro to remove these rows based on a value that is in a cell on each row.

the macro I have is as follows:

Sub deleteend()


Dim cell As Range
Windows("gamma.csv").Activate

For Each cell In Range("H2:H3500")
    If cell.Value = 5.42 Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

So as you can see, I want this to look in column H down to row 3500, if the value 5.42 is found I want that entire row to be removed.

The problem I am having, is that the macro is running, but not removing all rows that have this value. I have to run the macro multiple times for it to remove all rows, usually 3 ro 4 times.

There are about 250 rows in total that need to be removed, but the macro is only removing a small amount of these at any time.


Any thouhgts why this isnt working as expected?

 

[Peter_SSs]

Any thouhgts why this isnt working as expected?

Yes. The code is looking at H2 then H3, H4 etc
If both H2 and H3 are 5.42 then first the code checks H2, deletes that row and moves to H3. However the second 5.42 that was in H3 has moved up to H2 because row 2 was deleted. Hence that second 5.42 row does not get checked/deleted.

If deleting row-by-row you need to start at the bottom & work upwards.

However, if you only have a few thousand rows, try this way to delete them all at once.

Sub deleteend_v2()
  Windows("gamma.csv").Activate
  With Range("H1", Range("H" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=5.42
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

 

[grabrail]

Yes. The code is looking at H2 then H3, H4 etc
If both H2 and H3 are 5.42 then first the code checks H2, deletes that row and moves to H3. However the second 5.42 that was in H3 has moved up to H2 because row 2 was deleted. Hence that second 5.42 row does not get checked/deleted.

If deleting row-by-row you need to start at the bottom & work upwards.

However, if you only have a few thousand rows, try this way to delete them all at once.

Sub deleteend_v2()
  Windows("gamma.csv").Activate
  With Range("H1", Range("H" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, Criteria1:=5.42
    .Offset(1).EntireRow.Delete
    .Parent.AutoFilterMode = False
  End With
End Sub

Thank you, that works perfectly.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.