Delete rows that not contain string in my array

Question

Pls help me modify this code but I would like to keep it 90% the same.

I want to delete the rows that does not contain the array items. So my program deletes rows with a, b in cell. How can I modify the below code so that it erases the other a, b to remain in exec.

myArr = Array("a","b")
For I = LBound(myArr) To UBound(myArr)

    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet

        'Firstly, remove the AutoFilter
        .AutoFilterMode = False

        'Apply the filter
        .Range("E1:E" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)

        Set rng = Nothing
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With

        'Remove the AutoFilter
        .AutoFilterMode = False
    End With
Next I

Siddharth Rout · Accepted Answer · 2012-10-24 11:09:18Z

1

This works for me... I have commented the code so you should not have a problem understanding it...

Option Explicit

Dim myArr

Sub Sample()
    Dim ws As Worksheet
    Dim Lrow As Long, i As Long
    Dim rRange As Range, delRange As Range

    myArr = Array("a", "b", "c")

    Set ws = ThisWorkbook.Sheets("MySheet")

    With ws
        '~~> Get last row of Sheet
        Lrow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To Lrow
            If Not DoesExists(.Range("A" & i).Value) Then
                If delRange Is Nothing Then
                    Set delRange = .Range("A" & i)
                Else
                    Set delRange = Union(delRange, .Range("A" & i))
                End If
            End If
        Next i

        If Not delRange Is Nothing Then delRange.EntireRow.Delete
    End With
End Sub

Function DoesExists(clVal As Variant) As Boolean
    Dim j As Long

    For j = LBound(myArr) To UBound(myArr)
        If clVal = myArr(j) Then
            DoesExists = True: Exit For
        End If
    Next j
End Function

edited Oct 24, 2012 at 11:09

answered Oct 24, 2012 at 9:13

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

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

16 Comments

el el Over a year ago

but the efficiency is the same?

Siddharth Rout Over a year ago

You wanted something which is 90% similar to your code so I gave you one :) BTW did you test the code?

Siddharth Rout Over a year ago

Doesn't it delete all the occurrences? or have I misunderstood your query?

el el Over a year ago

it deletes same string..it delets a and b not the rest :D

el el Over a year ago

and i have one more question after you make the adjustment pls :D

|

Collectives™ on Stack Overflow

Delete rows that not contain string in my array

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related