Delete Rows using an array

[Crocdundee]

I wish to delete all rows with column A:A containing this text
Hi, I am using excel 2007
Data is in Column A:A
Rows change daily
I am using an array as this

myArray = Array("Turffontein", "Hastings", "Sha Tin","Kenilworth", "Thirsk")

My code deletes other rows which is not in the array

Dim myArray As Variant
Dim LR As Long
Dim j As Integer
Dim i As Integer
Application.ScreenUpdating = False
With Sheets("Sheet1")
myArray = Array("Turffontein", "Hastings", "Sha Tin", _
"Kenilworth", "Thirsk")
LR = .Range("D" & .Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
On Error Resume Next
With Range("D" & i)
j = Application.Match(.Value, myArray, True)
If Err <> 0 Then
Err.Clear
.EntireRow.Delete
End If
End With
Next i
End With
Application.ScreenUpdating = True
end sub

any help will be appreciated as usual
Graham

 

[Crocdundee]

LR = .Range("D" & .Rows.Count).End(xlUp).Row

should read LR = .Range("A" & .Rows.Count).End(xlUp).Row
same as
With Range("D" & i)
should read
With Range("A" & i)

Regards
Graham

 

[kevatarvind]

your data in column A and your macro is looking in column D

changes
this
LR = .Range("D" & .Rows.Count).End(xlUp).Row
to this
LR = .Range("A" & .Rows.Count).End(xlUp).Row

and

this
With Range("D" & i)
to this
With Range("A" & i)

 

[Crocdundee]

Ok I have done that and the result now is that it keeps all rows in the Array and deletes all others
I have changes this line
j = Application.Match(.Value, myArray, False)
to
j = Application.Match(.Value, myArray, True)

but still does not work properly

Graham

 

[kevatarvind]

try below code

Sub Test()
Dim lr As Long
Dim i As Integer
Sheets("Sheet1").Select
lr = Range("A" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
If Cells(i, 1).Value = "Turffontein" Or Cells(i, 1).Value = "Hastings" Or _
Cells(i, 1).Value = "Sha Tin" Or Cells(i, 1).Value = "Kenilworth" _
Or Cells(i, 1).Value = "Thirsk" Then Cells(i, 1).EntireRow.Delete
Next i
End Sub

 

[Crocdundee]

Thanks Kevatarvind for your help, works a treat
Really appreciate it

Regards
Graham

 

[kevatarvind]

Thanks For The Feedback

Your Welcome
Glad to Help

 

[Trebor76]

Hi Graham,

If you want to stick with the array concept (and for anyone else finding this thread based on its title), you can use this:

Option Explicit
Sub Macro2()

    'Written by Trebor76
    'Visit my website www.excelguru.net.au

    Dim varMyArray As Variant
    Dim lngMyRow As Long, _
        lngEndRow As Long
        
    Application.ScreenUpdating = False

    varMyArray = Array("Turffontein", "Hastings", "Sha Tin", "Kenilworth", "Thirsk")
    lngEndRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For lngMyRow = lngEndRow To 2 Step -1
        If IsNumeric(Application.Match(Range("A" & lngMyRow), varMyArray, 0)) = True Then
            Rows(lngMyRow).EntireRow.Delete
        End If
    Next lngMyRow
    
    Application.ScreenUpdating = True
    
    MsgBox "Any applicable rows have now been deleted."

End Sub

Regards,

Robert