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

detriez

Board Regular
Joined
Sep 13, 2011
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,271
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
' 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

Board Regular
Joined
Sep 13, 2011
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Thanks @Norie

This throws a Run-time error 13, Type mismatch at
Code:
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

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top