CODEWITHSUNDEEP
excelvba
Ben
Ben

Reputation: 107

Excel VBA deleting many different rows

I am using Excel 365 and am trying to delete many difference rows and have some working code but it is very inefficient. Deleting 500 out of about 2000 rows takes a long time compared to manually filtering the column, highlighting the rows and deleting so I thought to get all the rows needing to be deleted and do it in one shot but have not been successful. My working code is:

' When cell contains TRUE, erase that row
For i = LastRow To firstDataRow Step -1
    If Cells(i, eraseCol).Value = True Then
        sheetdata.Rows(i).Delete
        LastRow = LastRow - 1
    End If
Next

What I am trying to do which doesn't work:

For i = LastRow To firstDataRow Step -1
    ' collect all rows to be deleted in range r
    If Cells(i, eraseCol).Value = True Then
        r = Union(Rows(i), r)
    End If
Next
r.Delete

Any idea how I can get all rows to be deleted in r and do it at once? Or perhaps there is a better way?

Upvotes: 0

Views: 73

Answers (2)

Ted D.
Ted D.

Reputation: 268

' collect rows to be deleted into range r

' Alternate loop construct using iterator (partial code - diff only)
' Dim i as Range
' For Each i in <column range> ' i.e. <EraseCol>2:<EraseCol>10000
'     If i.value Then
'         If r Is Nothing Then
'             Set r = i
'         Else
'             Set r = Union(r, i)
'         End If
'     End If
' Next i

' No need to step backward, this will only scroll up one time.
For i = firstDataRow to LastRow
    If Cells(i, eraseCol).Value Then
        If r Is Nothing Then
            Set r = Rows(i)
        Else
            Set r = Union(r, Rows(i))
        End If
    End If
Next i
r.EntireRow.Delete ' NO UNDO - wipes undo buffer

Upvotes: 2

Scott Holtzman
Scott Holtzman

Reputation: 27259

You are close. Give this a shot:

For i = LastRow To firstDataRow Step -1
    ' collect all rows to be deleted in range r
    If Cells(i, eraseCol).Value Then 
        If r is Nothing Then set r = cells(i,eraseCol) else: Set r = union(r,Cells(i,eraseCol))
    End If
Next

r.EntireRow.Delete

N.B - since you are only collecting cells into a range, you can loop forward if you desire; but looping backward will still work too

Upvotes: 0

Related Questions