A more efficient way to delete row when there is a match to a named range

[detriez]

I've got a named range (nPersonalEmailDomain) with around 3700 email domains... gmail.com, yahoo.com etc

I need to delete the entire row of another sheet where an email in Column C is on the nPersonalEmailDomain list. This sheet can have several hundred to several thousand rows

So, If an email in Column C = bob@gmail.com, delete the entire row

This hangs for about 8 minutes before it finally completes processing 300 rows

Is there a more efficient way to do this?
Rather than looking in each email for the domain on the named range, maybe I should put the email domain from the email to another column and then do an exact match ?
What might the below code look like to delete the entire row if Column BH equals nPersonalEmailDomain?

Sub PersonalEmails(Control As IRibbonControl)

Dim nRng As Range, rng As Range, c As Range, n As Range

Set nRng = Range("nPersonalEmailDomain") 'my named range containing the list of personal email domains
Set rng = Range("C2", ActiveSheet.Cells(Rows.Count, "c").End(xlUp)) 'get col C parameters

For Each c In rng 'Look at each item in col C
  For Each n In nRng
    If InStr(c.Text, n.Text) > 0 Then
      c.EntireRow.Delete 'Delete rows with match to named range
      Exit For
    End If
  Next
Next

End Sub

 

[Norie]

Loop through the emails, extract the domain, use Application.Match to see if it's in the named range and if it is delete the row.

Something like this.

' loop from bottom up when deleting
For idx = ActiveSheet.Cells(Rows.Count, "C").End(xlUp) to 2 Step -1
    strDomain = Split(Cells(idx, "C"), "@")(1)
    Res = Application.Match(strDomain, Range("nPersonalEmailDomain"), 0)
    If Not IsError(Res) Then
        Rows(idx).Delete
    End If
Next idx

 

[detriez]

Thanks @Norie

This throws a Run-time error 13, Type mismatch at

For idx = ActiveSheet.Cells(Rows.Count, "C").End(xlUp) To 2 Step -1

I tried Dim'ing idx as Range, Long, Integer and String but that didn't work

Sub PersonalEmails(Control As IRibbonControl)

Dim nRng As Range, rng As Range, c As Range, n As Range


Set nRng = Range("nPersonalEmailDomain") 'my named range containing the list of personal email domains
Set rng = Range("C2", ActiveSheet.Cells(Rows.Count, "c").End(xlUp)) 'get col C parameters


For idx = ActiveSheet.Cells(Rows.Count, "C").End(xlUp) To 2 Step -1
    strDomain = Split(Cells(idx, "C"), "@")(1)
    Res = Application.Match(strDomain, Range("nPersonalEmailDomain"), 0)
    If Not IsError(Res) Then
        Rows(idx).Delete
    End If
Next idx

End Sub