Delete rows based on list on aother sheet


I need to delete rows on one sheet from a list on another sheet. Since the data-set is huge, I can't do this one line at a time so thought using autofilter would be faster.


Filter Sheet "Data" by the names in column B of sheet "Table"
Delete all rows that are not filtered (do not delete rows where column C contains the names in column B of sheet "Table".)

1.  List of names in column B of sheet "Table" can change and the number could change so need to create a dynamic array?
2.  Number of rows on sheet "Data" will be many, many thousands so deleting one row at a time will not work.

Example workbook attached.

Thanks in advance,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ejgil HedegaardCommented:
Insert this formula in E2 and copy down
Sets a 0 if there is a match to Table, and 1 if not.
Copy all the formulas in column E, and paste as values.
Sort on column E.
Search column E for the first occurrence of 1, and delete all values below that.
Delete column E.
swjtx99Author Commented:
Hi Ejgil Hedegaard,

Thanks for the reply but was looking for a VBA solution. I apologize for not being more specific.


byundtMechanical EngineerCommented:
Here is a macro that puts in a COUNTIF formula in worksheet Data to test whether the names are found in worksheet Table. Worksheet data is then sorted by the results of that formula, and the rows with non-matching names are deleted.
Sub DeleteNoNames()
Dim rgData As Range, rgMaster As Range, rgTest As Range, rgFormula As Range
Dim v As Variant
Application.ScreenUpdating = False
With Worksheets("Table")
    Set rgMaster = .Range("B2") 'First name on list
    Set rgMaster = Range(rgMaster, .Cells(.Rows.Count, rgMaster.Column).End(xlUp))
End With
With Worksheets("Data")
    Set rgTest = .Range("C2")   'First name to be tested
    Set rgTest = Range(rgTest, .Cells(.Rows.Count, rgTest.Column).End(xlUp))
    Set rgData = rgTest.CurrentRegion
    Set rgData = rgData.Resize(rgData.Rows.Count, rgData.Columns.Count + 1)
    Set rgFormula = rgData.Cells(2, rgData.Columns.Count).Resize(rgData.Rows.Count - 1)
    rgFormula.FormulaR1C1 = "=COUNTIF(" & "'" & rgMaster.Worksheet.Name & "'!" & rgMaster.Address(ReferenceStyle:=xlR1C1) & ",RC" & rgTest.Column & ")>0"
    rgData.Sort Key1:=rgFormula.Cells(0, 1), Order1:=xlAscending, Header:=xlYes
    If rgFormula.Cells(1, 1) = False Then
        v = Application.Match(True, rgFormula, 0)
        If IsError(v) Then
            rgFormula.Cells(1, 1).Resize(v).EntireRow.Delete
        End If
    End If
End With
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swjtx99Author Commented:
Hi byundt,

Works! Tried it with 300K rows and it took less than a minute.

Thanks for your help,

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.