CODEWITHSUNDEEP
vbaexcel
MTJ
MTJ

Reputation: 99

VBA: Searching substring and deleting entire row

I'm trying to delete all rows which have a string that contains "H" in the P column. The macro works, however, it only deletes half of the necessary rows each time. This is because of the For loop in the code--when a row is deleted, the next row will have the same i value as the deleted one, and is skipped by the Next i.

Dim LastRow As Long

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.count, "P").End(xlUp).Row
End With

'Iterates through rows in column B, and deletes the row if string contains "H"
For i = 4 To LastRow
    If InStr(1, Range("P" & i), "H") <> 0 Then Rows(i).EntireRow.Delete
Next i

'Message Box when tasks are completed
  MsgBox "Complete"

Is there a way to have the For loop repeat the same i value if a row is deleted in order to get all of the rows?

Upvotes: 1

Views: 1559

Answers (2)

user4039065
user4039065

Reputation:

Try filtering for the wildcard *H* and deleting the visible rows.

Option Explicit

    Sub qweqrwtqrweq()
        if autofiltermode then .autofiltermode = false
        With ActiveSheet  '<~~much better to use thge ACTUAL WORKSHEET NAME!! e.g. with worksheets("sheet1")
            With .Range(.Cells(4, "P"), .Cells(.Rows, Count, "P").End(xlUp))
                .AutoFilter fiedl:=1, Criteria1:="*h*"
                If CBool(Application.subtotla(103, .Cells)) Then
                    .Cells.etirerow.Delete
                End If
            End With
        End With
        if autofiltermode then .autofiltermode = false
        MsgBox "Complete"
    End Sub

Bulk operations are almost always more efficient than row-by-row examinations.

Upvotes: 0

mechanical_meat
mechanical_meat

Reputation: 169284

The standard way to do this is to iterate in reverse order.

Dim LastRow As Long

'Finds last row
With ActiveSheet
    LastRow = .Cells(.Rows.count, "P").End(xlUp).Row
End With

'Iterates in reverse through rows in column B, and deletes the row if string contains "H"
For i = LastRow To 4 Step -1 
    If InStr(1, Range("P" & i), "H") <> 0 Then Rows(i).EntireRow.Delete
Next i

'Message Box when tasks are completed
  MsgBox "Complete"

Upvotes: 2

Related Questions