Look through a list in one workbook, find those values in multiple other workbooks and delete rows when found

[OJMcGee]

Hi,

I'm very new to VBA and looking for help with this task.

I have a list of 50 or so reference numbers - I need to search for these references in 120 different workbooks and where I find any of those references, delete the entire row from that workbook.

Can anyone help?

Thanks

 

[mumps]

We will need more information:
-In which column are the reference numbers and in what range?
-What is the full path to the folder containing the 120 files?
-What is the extension of those files (xlsx, xlsm)?
-Are they the only files in that folder?
-Will the workbook containing the macro also be saved in that same folder?
-Do you want to search only one sheet or all sheets in the different workbooks?
-Are the reference numbers to be searched found in the same column in all the workbooks or can they be anywhere in the sheet?

 

[OJMcGee]

We will need more information:
-In which column are the reference numbers and in what range?
-What is the full path to the folder containing the 120 files?
-What is the extension of those files (xlsx, xlsm)?
-Are they the only files in that folder?
-Will the workbook containing the macro also be saved in that same folder?
-Do you want to search only one sheet or all sheets in the different workbooks?
-Are the reference numbers to be searched found in the same column in all the workbooks or can they be anywhere in the sheet?

-The references are all in column A of each sheet and the number of rows averages around 45,000 per sheet, columns go to AF
-Full path for the folder is C:\Users\ojmcgee\Desktop\References to be removed
-File extension is .xlsx
-They are the only files in this folder
-The workbook with the macro can also be saved in this folder, but doesn't need to be
-Only looking to search on Sheet1
-The references to be searched are all in the same column (A) in each of the workbooks

Thank you

 

[mumps]

Place this macro in a regular module and save the workbook in the same folder as the other files. The macro assumes your reference values in the macro workbook start in row 2 of column A. Change the range (in red) to suit your needs.

Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, wkbSource As Workbook, strExtension As String, arr As Variant, i As Long, strPath As String
    Set ws = ThisWorkbook.Sheets(1)
    arr = ws.Range("A2", ws.Range("A" & Rows.Count).End(xlUp)).Value
    strPath = ThisWorkbook.Path
    strExtension = Dir(strPath & "\*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & "\" & strExtension)
        For i = LBound(arr) To UBound(arr)
            With Sheets(1).Columns("A")
                .Replace what:=arr(i, 1), replacement:=""
                On Error Resume Next
                .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                On Error GoTo 0
            End With
        Next i
        wkbSource.Close False
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub