Delete row when cell is not equal a string in an array

Question

I am trying to loop through an array and, when it finds a cell which is not equal a specific value, it deletes the entire row. Here is the code:

Sub DeleteTest()

Dim crr()

crr = Range("A3:A1000")

For i = LBound(crr, 1) To UBound(crr, 1)
    If (crr(i, 1) <> "One" And crr(i, 1) <> "Two") Then
       ' Line to delete the row in which the value of the cell is not One or Two

    End If
Next

End Sub

I know I can also do it with an Autofilter, but I would like to know the way to do it with the array.

Create a union of the row numbers then delete all the rows at once after the loop. — Scott Craner
– Scott Craner, Commented Apr 4, 2020 at 21:03

Tim Williams · Accepted Answer · 2020-04-04 21:19:35Z

1

Here's one way:

Sub DeleteTest()

    Dim rng As Range, crr(), i As Long

    Set rng = Range("A3:A1000")
    crr = rng.Value

    For i = UBound(crr, 1) To LBound(crr, 1) Step -1 '<<< loop backwards
        If (crr(i, 1) <> "One" And crr(i, 1) <> "Two") Then
           rng.Cells(i).EntireRow.Delete
        End If
    Next

End Sub

answered Apr 4, 2020 at 21:19

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jorge Over a year ago

Thank you! The code worked. What would be the reason to loop backwards? Is it the same as introducing "For i = LBound(crr, 1) To UBound(crr, 1)"?

Tim Williams Over a year ago

If you start by deleting row 1, then where does row 2 go?

YasserKhalil · Accepted Answer · 2020-04-05 17:03:35Z

0

Try this code

Sub Test()
Dim x, r As Range
With ThisWorkbook.Sheets("Sheet1")
    Set r = .Range("A3:A1000")
    x = Filter(.Evaluate("TRANSPOSE(IF((" & r.Address & "=""One"")+(" & r.Address & "=""Two""),""A"" & ROW(" & r.Address & ")))"), False, False)
    If UBound(x) = -1 Then Exit Sub
    .Range(Join(x, ",")).EntireRow.Hidden = True
    On Error Resume Next
        r.SpecialCells(xlCellTypeVisible).EntireRow.Delete
    On Error GoTo 0
    .Rows.Hidden = False
End With
End Sub

answered Apr 5, 2020 at 17:03

YasserKhalil

9,60813 gold badges62 silver badges135 bronze badges

Collectives™ on Stack Overflow

Delete row when cell is not equal a string in an array

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related