• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 65
  • Last Modified:

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.

1 Solution
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now