excel - VBA macro to delete rows quickly

Question

I have several really large excel data files and I need to go through them all and delete all rows where the value of the cell in column T is 1. Right now my code looks like:

Sub test()
    Dim cell As Range

    For Each cell In Worksheets("Sheet1").Range("T5", "T900000")
        If cell.Value = 1 Then
            cell.EntireRow.Delete
        End If
    Next cell
End Sub

It seems to be working, but takes forever to run and I'm going to have to do this a bunch of times. Is there a better way of doing this, or some way to optimize what I already have to make it run faster?

Answer 1

This doesn't work as you think... When you delete rows as you iterate through them, you end up skipping rows. Example: imagine your rows have the numbers 1...10 in column A. You look at the first row and decide to delete it. Now you look at the second row. It has the number 3! You never looked at row 2!!

Better method would be to filter the spreadsheet on your criteria for column T, copy it, paste it I to a new worksheet (with formatting etc).

You can turn on macro recording and do this manually; then you will have the exact VBA code. I am sure that will be much faster.

Even if you don't do that, if you want to do a for each where you delete things, reverse the order (start at the end and work backwards)