Delete Rows in Excel which match criteria

I have around 20 Excel files with around a total of 5000 rows.  I have a further list with around 400 rows.

I need to delete the rows in the 20 excel files which contain the list of 400 rows.


delete all rows in excel with data where row b contains one of the values in excel with criteria

excel file with data
row a    | row b | row c
martin  | boy     | young
roy        | boy     | young
steve    | boy     | old
anna    | girl      | young
afgi       | unknown | boy

excel file with criteria
row a

thank you for the input on how to delete the rows in excel with data which contain the values in excel with criteria.

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.

Roy CoxGroup Finance ManagerCommented:
Any code to do that will be very slow.

You need to provide an example of the layout of criteria workbook.

Also, where are the files stored in relation to each other?
whenzAuthor Commented:
i have around 20 of those excel files:
excel file with rows to delete

the excel with criteria contains the following:
excel with criteria

I need to delete the rows in the first screenshot with the docnumber which are listed in the second screenshot.
Roy CoxGroup Finance ManagerCommented:
Example workbooks are what I meant
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may give this a try...

The code assumes that...
1) You have a separate file for all the doc numbers criteria to be deleted in column A on Sheet1.
2) All the files in which rows are to be deleted with the doc numbers listed in the above criteria file are saved in a single folder.
3) The doc numbers are in the column B in all the files.

Here is how the code would work:
1) The code will first prompt you to select the criteria file with doc numbers.
2) The code will then prompt you to select a folder with all the files in which you want to delete the rows.

Note: If you want to implement this code to another workbook, you will need to add the reference to a library called Microsoft Scripting Runtime.

Sub DeleteRows()
Dim wbCriteria As Workbook, dwb As Workbook
Dim wsCriteria As Worksheet, dws As Worksheet
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Dim sFolder As Folder
Dim sFolderPath As String
Dim sFilePath As String
Dim x
Dim i As Long, lr As Long
Dim docNumbers()

With Application
    .Calculation = xlCalculationManual
    .EnableEvents = False
    .ScreenUpdating = False
End With

Set fso = New Scripting.FileSystemObject

With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select The Excel File With Deletion Criteria!"
    .Filters.Add "Excel Files", "*.xl*"
    If .Show = -1 Then
        sFilePath = .SelectedItems(1)
        MsgBox "You didn't select the Excel Criteria File.", vbExclamation
        Exit Sub
    End If
End With

Set wbCriteria = Workbooks.Open(sFilePath)
Set wsCriteria = wbCriteria.Sheets(1)

With wsCriteria
    x = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With

ReDim docNumbers(1 To UBound(x, 1))

For i = 1 To UBound(x, 1)
    docNumbers(i) = CStr(x(i, 1))
Next i

wbCriteria.Close False

With Application.FileDialog(msoFileDialogFolderPicker)
   .Title = "Select The Folder With Excel Files!"
   .ButtonName = "Confirm"
   If .Show = -1 Then
      sFolderPath = .SelectedItems(1)
      Set sFolder = fso.GetFolder(sFolderPath)
      MsgBox "You didn't select a folder.", vbExclamation, "Folder Not Selected!"
      Exit Sub
   End If
End With

For Each fil In sFolder.Files
    If Left(fso.GetExtensionName(fil), 2) = "xl" And fil.Path <> sFilePath Then
        Set dwb = Workbooks.Open(fil)
        Set dws = dwb.Sheets(1)
        lr = dws.Cells(Rows.Count, 2).End(xlUp).Row
        With dws.Range("A1").CurrentRegion
            .AutoFilter 2, docNumbers, 7
            If dws.Range("B1:B" & lr).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
                dws.Range("B2:B" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            End If
        End With
        dwb.Close True
    End If
Next fil
With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

MsgBox "Rows have been deleted successfully from all the files.", vbInformation
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
Do all the files use the same column header?
Does the criteria file use the same column header?
Matt NicholasBusiness AnalystCommented:
Piece of cake.

Add a column to the list of 400 (second dataset) and assign each record/row "L"
Convert both datasets to tables (CTRL+T)
Import them individually to Power Query (Data tab > Get External Data > From Table/Range)
Merge both tables using the Document ID as the KEY (Merge Queries top of ribbon)
Filter the new column and remove all records with "L"
Close and Load
Have a beer

Basically, merging the two tables will match records of both datasets where the document ID matches and remove all "L" because you filtered the set prior to load.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Answered the original question.
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 Applications

From novice to tech pro — start learning today.