VBA Reference a named range in the file

[detriez]

@DonkeyOte posted this great code HERE for deleting records with a hotmail.com domain

I have a named range [nEmailDomains] on the fle with a number of domains
I need to do the exact same except I need to reference nEmailDomains and delete every entry with a domain on the list rather than just hotmail.com

Dim rng As Range
Columns(3).AutoFilter Field:=1, Criteria1:="=*@hotmail.com"
With ActiveSheet.AutoFilter.Range
    Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
    Application.DisplayAlerts = False
    If Not rng Is Nothing Then rng.Delete
    Application.DisplayAlerts = True
End With
Columns(3).AutoFilter

 

[JLGWhiz]

This would substitute your named range for the Columns(3) reference. But wouldn't it be simpler to just do a

Range("nEmailDomains").ClearContents

or do you have other data in that range?

Sub t()
Dim rng As Range
Range("nEmailDomains").AutoFilter Field:=1, Criteria1:="=*@*"
With ActiveSheet.AutoFilter.Range
    Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
    Application.DisplayAlerts = False
    If Not rng Is Nothing Then rng.Delete
    Application.DisplayAlerts = True
End With
Range("nEmail.Domains").AutoFilter
End Sub

 

[detriez]

Thanks @JLGWhiz

I'm sorry I wasn't more clear

I need to delete the entire row when Column 3 (my Email column) has one of the values from the nEmailsDomain list

I'm thinking something like this

Dim rng As Range

Columns(3).AutoFilter Field:=1, Criteria1:=Range("nEmailDomains")
With ActiveSheet.AutoFilter.Range
    Set rng = .Offset(1, 0).SpecialCells(xlCellTypeVisible)
    Application.DisplayAlerts = False
    If Not rng Is Nothing Then rng.Delete
    Application.DisplayAlerts = True
End With
Columns(3).AutoFilter

 

[detriez]

The latest code I posted throws a method 'Range' of object '_Global' failed error

 

[detriez]

Thank you!!
I got it sorted. My range was actually nEmailDomain not ...Domains
Thank you for your help

 

[JLGWhiz]

Thanks for the feedback,
regards, JLG