Delete entire rows that contain any of the values contained in a seperate list

[mritchi2]

Hi,

I'm fairly new to VBA, I'm trying to pick it up by recording macros and editing them, with a little bit of help from Google and forums like these.

I have a master list of customers contained on one sheet ("MasterList"), including various details across numerous columns on the same row. I also have a list of email addresses that have unsubscribed (which are contained on the "Unsubscribed" sheet) and must therefore be removed from the master list so they are not contacted in the future. Therefore, I want to loop through each cell contained in the Unsubscribed list (Column A) and check if the value is present in the master list (Column I). If it is, I want to delete the entire row of data. If it is not, I want to move on to the next value in the list.

I have created the following macro but the coding is not quite right... Can anyone help point me in the right direction?

Sub DeleteRows()

Application.ScreenUpdating = False

Dim MyCell As Range
Dim MyRange As Range

Set MyRange = Worksheets("Unsubscribed").Range("A:A")
Set MyCell = Worksheets("Unsubscribed").Range("A2")

[COLOR=#ff0000]For Each MyCell In MyRange.Cells     <--Debug error comes up for this phrase.[/COLOR]

With Sheets("MasterList")
    .AutoFilterMode = False
    .Range("i:i").AutoFilter
    .Range("i:i").AutoFilter Field:=1, Criteria1:=MyCell    
End With
   
    Application.DisplayAlerts = False
    ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True

Next MyCell
    
ActiveSheet.ShowAllData

Sheets("Unsubscribed").Activate
Application.ScreenUpdating = True

End Sub

Any help would be much appreciated.

Regards,

Michael.

 

[Momentman]

Simply change it to:

For Each MyCell in MyRange

rather than

For Each MyCell In MyRange.Cells

 

[kevatarvind]

try below code

Option Explicit
Sub Test()
Dim lkrng As Range
Dim my As Variant
Dim lr As Long
Dim i As Integer
Set lkrng = Sheets("Unsubscribed").Range("A:A")
Sheets("MasterList").Select
lr = Range("I" & Rows.Count).End(xlUp).Row
For i = lr To 2 Step -1
On Error Resume Next
my = WorksheetFunction.Match(Cells(i, 9), lkrng, 0)
If Err.Number = 1004 Then
GoTo nex
Else
Cells(i, 9).EntireRow.Delete
End If
nex:
Next
End Sub

 

[mritchi2]

Kevatarvind, your code works perfectly! AMAZING!
I really have to work on my understanding of how to effectively use dim variables, it seems to be the answer to every problem I encounter.

Momentman, your solution fixed the debug issue with that particular phrase. Unfortunately there were more issues with my original coding, it's all a learning experience though.

Thanks a lot for your help guys!

 

[kevatarvind]

Thanks For The Feedback

Your Welcome