CODEWITHSUNDEEP
vbaexcelrow
Selrac
Selrac

Reputation: 2293

VBA slow process for removing rows based on condition

I have a VBA Excel code with that checks values in a specific column. If the row in that column contains the value 'Delete' and then deletes the row.

The code works well, but it is really slow. Any ideas on how to get the code run faster?

    Dim rng1 As Range
    Dim i As Integer, counter As Integer
    'Set the range to evaluate to rng.
    Set rng1 = Range("g1:g1000")
    'initialize i to 1
    i = 1
    'Loop for a count of 1 to the number of rows
    'in the range that you want to evaluate.
    For counter = 1 To rng1.Rows.Count
        'If cell i in the range1 contains an "Delete"
        'delete the row.
        'Else increment i
        If rng1.Cells(i) = "Delete" Then
            rng1.Cells(i).EntireRow.Delete
        Else
            i = i + 1
        End If
    Next

Thanks

c.

Upvotes: 1

Views: 435

Answers (3)

Tibo
Tibo

Reputation: 393

Try sorting the rows (on collumn G) then deleting all marked ("delete") rows in one action. That is much faster.

Upvotes: 0

Selrac
Selrac

Reputation: 2293

I managed to find a solution with the Autofilter function.

Hope it helps someone

    Selection.AutoFilter
    Set ws = ActiveWorkbook.Sheets("UploadSummary")
    lastRow = ws.Range("G" & ws.Rows.Count).End(xlUp).Row
    Set rng = ws.Range("G1:G" & lastRow)
    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=7, Criteria1:="delete" ' 7 refers to the 7th column
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

Upvotes: 0

Andy Brazil
Andy Brazil

Reputation: 134

Sub deletingroutine()
  Dim r As Range
  For Each r In Range("g1:g1000")
     If r = "delete" Then r.EntireRow.Delete

  Next r
End Sub

Upvotes: 1

Related Questions