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-24T11:09:18.980

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 '12 at 11:09

answered Oct 24 '12 at 09:13

Siddharth Rout

147,039
17
206
250

but the efficiency is the same? – el el Oct 24 '12 at 10:12
You wanted something which is 90% similar to your code so I gave you one :) BTW did you test the code? – Siddharth Rout Oct 24 '12 at 10:15
Doesn't it delete all the occurrences? or have I misunderstood your query? – Siddharth Rout Oct 24 '12 at 10:34
it deletes same string..it delets a and b not the rest :D – el el Oct 24 '12 at 10:50
and i have one more question after you make the adjustment pls :D – el el Oct 24 '12 at 10:54
how can i make a button in excel that opens my user form? but the button to be on every excel i open – el el Oct 24 '12 at 10:59
i think so...i need a fast one because i will be deleting 10k of rows :D – el el Oct 24 '12 at 11:07
I have updated the code above. Try it now... If your array has just two items then we can use Autofilter.... In the above example, I have taken 3 values – Siddharth Rout Oct 24 '12 at 11:09
ok i will but i can t right now because i am running something else.. i will reply in one h top...and the other problem ?:D with the button? – el el Oct 24 '12 at 11:11
Can you explain this part? `but the button to be on every excel i open ` – Siddharth Rout Oct 24 '12 at 11:12
now i open the user form this way ( alt+ f11 ) and then i run f5... i want a button on the excel sheet that will open my user form when i press it...but i want it on every excel i open to be there.. – el el Oct 24 '12 at 11:18
You will have to create an Add-In. If you are using Excel 2007 onwards then you might also want to go down this route... http://stackoverflow.com/questions/8850836/how-to-add-a-custom-ribbon-tab-using-vba – Siddharth Rout Oct 24 '12 at 11:21
so i need another add in? i can t simply put code on that button ? somthing like userform.show? or something else? – el el Oct 24 '12 at 11:23
If you already have an Add-In then create a button in the toolbar and then use Userform1.show in that button – Siddharth Rout Oct 24 '12 at 11:28
so i create in toolbar not in developer? how can i create in toolbar button?...sry for beeing such a pain :D – el el Oct 24 '12 at 11:32
I tested it before posting... Can you upload a workbook so I can test it – Siddharth Rout Oct 24 '12 at 12:59

Delete rows that not contain string in my array

1 Answers1