Spreadsheet: 29526d1224594638-macro-to-search-multiple-documents-and-delete-rows-deletecodeedited
Sheet: Sheet1

 | Hi Excel user
 | You can find the whole webpage on this sheet and all code examples in separate modules in the VBA editor
 | Use the Tips on the webpage to change the examples in the VBA editor.
 | You can use the data sheet in this workbookto test the examples.
 | [NAME]
**********************************************************************************************************
 | Delete row if a specific value exist (VBA)
 | [NAME] (last update 15-July-2007)
 | Go back to the Excel tips page
 | Tip: If you get a headache when you see the code then you can try my EasyFilter add-in.
 | This add-in have a option to delete rows.(there is no version for Excel 2007 on this moment)
 | You can find it here:
 | http://www.rondebruin.nl/easyfilter.htm
 | There are a few different ways to delete rows :
 | Loop backwards through all rows
 | Loop backwards through all rows and use Union
 | Use AutoFilter to delete rows
 | Use Find to delete rows
 | Use Specialcells to delete rows
 | 1: Loop backwards through all rows
 | You find different examples on this part of the page to loop through the rows and check
 | If a specific value exists in a column (A column in my example) and delete that row if it exists.
 | The example macro loop through all the rows in the UsedRange.
 | This is the first row with data till the last row with data on your worksheet.
 | It can be slower if the column that you check doesn’t use so many rows or if your UsedRange is
 | bigger then you data. You can find more information on [NAME] website about this.
 | http://www.contextures.on.ca/xlfaqApp.html#Unused
 | You can replace this part of the macro
 | Firstrow = .UsedRange.Cells(1).Row
 | [NAME].Rows(.UsedRange.Rows.Count).Row
 | With this if you want to fill in the Firstrow and the [NAME] yourself.
 | Firstrow = 4
 | [NAME] = 100
 | Or with this if you want to fill in the [NAME] and let the code find the [NAME] in the column.
 | Firstrow = 1
 | [NAME] = .Cells(.Rows.Count, "A").End(xlUp).Row
 | Code example
 | The code below will delete every row in the usedrange with "ron" in the A column.
 | If .Value = "ron" Then .EntireRow.Delete
 | I use the A column in my example, change the A to your column in this code line.
 | With .Cells(Lrow, "A")
 | Change ron and the A to your search word and column before you try the macro.
 | See the examples below the macro if you want to do something different.
 | Sub Loop_Example()
 | Dim Firstrow As Long
 | [NAME]
 | Dim Lrow As Long
 | Dim CalcMode As Long
 | Dim ViewMode As Long
 | With Application
 | CalcMode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'We use the ActiveSheet but you can replace this with
 | 'Sheets("MySheet")if you want
 | With ActiveSheet
 | 'We select the sheet so we can change the window view
 | .Select
 | 'If you are in Page Break Preview Or Page Layout view go
 | 'back to normal view, we do this for speed
 | ViewMode = ActiveWindow.View
 | ActiveWindow.View = xlNormalView
 | 'Turn off Page Breaks, we do this for speed
 | .DisplayPageBreaks = False
 | 'Set the first and last row to loop through
 | Firstrow = .UsedRange.Cells(1).Row
 | Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 | 'We loop from [NAME] to Firstrow (bottom to top)
 | For Lrow = [NAME] To Firstrow Step -1
 | 'We check the values in the A column in this example
 | With .Cells(Lrow, "A")
 | If Not IsError(.Value) Then
 | If .Value = "ron" Then .EntireRow.Delete
 | 'This will delete each row with the Value "ron"
 | 'in Column A, case sensitive.
 | End If
 | End With
 | Next Lrow
 | End With
 | ActiveWindow.View = ViewMode
 | With Application
 | .ScreenUpdating = True
 | .Calculation = CalcMode
 | End With
 | End Sub
 | Examples to change the code
 | All macro’s will delete rows with "ron" in the A column
 | If .Value = "ron" Then .EntireRow.Delete
 | 'This will delete each row with the Value "ron" in Column A, case sensitive.
 | You can replace the two lines above with one of the examples below
 | Note: I use text examples like ="ron" but you can also use = 1200 for
 | numeric columns or use >, <, >=, <=
 | Tip: If you want to delete all rows that not have "ron" in column A then replace = for <>
 | If .Value Like "*ron*" Then .EntireRow.Delete
 | 'This will delete each row where "ron" is a part of the string, case sensitive.
 | ' Use "*ron" for a value that start with [NAME] or "ron*" for a value that ends with ron
 | If LCase(.Value) =  LCase("ron") Then .EntireRow.Delete
 | 'This will delete each row with the Value "ron" in Column A, not case sensitive.
 | If Trim(.Value) = "ron" Then .EntireRow.Delete
 | 'It uses the Trim function to strip the spaces
 | 'This will delete each row with the Value "ron" in Column A, case sensitive.
 | If Trim(LCase(.Value)) =  LCase("ron") Then .EntireRow.Delete
 | 'It uses the Trim function to strip the spaces
 | 'This will delete each row with the Value "ron" in Column A, not case sensitive.
 | If .Value = "" Then .EntireRow.Delete
 | 'This will delete each row if the cell is empty or have a formula that evaluates to ""
 | If IsEmpty(.Value) Then .EntireRow.Delete
 | 'This will delete the row if the cell is empty
 | Select Case .Value
 | Case Is = "jelle", "ron", "dave": .EntireRow.Delete
 | End Select
 | 'Use Select Case if you want to check more values in the cell
 | If Not IsError(Application.Match(.Value, _
 | Array("jelle", "ron", "dave"), 0)) Then .EntireRow.Delete
 | 'Or use this one with Application.Match if you want to check more values.
 | 'in the cell. You can also use a range with the values to delete.
 | 'Replace Array("jelle", "ron", "dave")  with  Sheets("Sheet1").Range("A1:A200")
 | Tip: To make it easier to edit you can define a variable to hold that array
 | ArrNames = Array("jelle", "ron", "dave")
 | If Not IsError(Application.Match(.Value, ArrNames, 0)) Then .EntireRow.Delete
 | Do not forget to add this dim line
 | Dim ArrNames As [NAME]
 | Check a whole row or more columns
 | Replace this part of the macro with one of the code examples below
 | 'We check the values in the A column in this example
 | With .Cells(Lrow, "A")
 | If Not IsError(.Value) Then
 | If .Value = "ron" Then .EntireRow.Delete
 | 'This will delete each row with the Value "ron"
 | 'in Column A, case sensitive.
 | End If
 | End With
 | Replace the code above with the code below
 | If .Cells(Lrow, "A").Value = "ron" And _
 | .Cells(Lrow, "B").Value = "dave" And _
 | .Cells(Lrow, "C").Value > 10 Then .Rows(Lrow).Delete
 | 'Use this if you want to check for values in other columns also.
 | If Application.CountA(.Rows(Lrow)) = 0 Then .Rows(Lrow).Delete
 | 'This will delete the row if the whole row is empty (all columns)
 | If Application.CountA(.Range(.Cells(Lrow, "A"), .Cells(Lrow, "E"))) = 0 Then .Rows(Lrow).Delete
 | 'This will delete the row if the first 5 cells in the row are empty
 | If Application.CountIf(.Rows(Lrow), "ron") = 0 Then .Rows(Lrow).Delete
 | ' Delete each row if the value "ron" not exist in the row (It will look in the whole row)
 | If Application.CountIf(.Rows(Lrow), "ron") > 0 Then .Rows(Lrow).Delete
 | ' Delete each row if the value "ron" exist in the row (It will look in the whole row)
 | Tip: With [NAME] you can use wild cards like this "*ron*" (is also working then if "ron" is a part of a cell)
 | Use "*ron" for a value that start with [NAME] or "ron*" for a value that ends with ron.
 | See the Excel help for more information about CountIf, there are examples there
 | about other wildcards like ?.
 | 2: Loop backwards through all rows and use Union
 | This example will loop through all rows in the usedrange and use Union to make a range (rng)
 | with all cells with the value "ron". Then it delete the rows in one time with [NAME],
 | this will be faster then deleting in the loop like we do in the example above.
 | Note: you can use a lot of tips/examples from the example above in this union example.
 | If you have problems you can always ask a question in the Excel newsgroups.
 | Note: There is a maximum of 8192 separate areas when you use Union.
 | See this page for more info  http://www.rondebruin.nl/specialcells.htm
 | Code example
 | The code below will delete every row in the usedrange with "ron" in the A column.
 | If .Value = "ron" Then .EntireRow.Delete
 | I use the A column in my example, change the A to your column in this code line.
 | With .Cells(Lrow, "A")
 | Change ron and the A to your search word and column before you try the macro.
 | Sub Union_Example()
 | Dim Firstrow As Long
 | [NAME]
 | Dim Lrow As Long
 | Dim CalcMode As Long
 | Dim ViewMode As Long
 | Dim rng As Range
 | With Application
 | CalcMode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'We use the ActiveSheet but you can replace this with
 | 'Sheets("MySheet")if you want
 | With ActiveSheet
 | 'We select the sheet so we can change the window view
 | .Select
 | 'If you are in Page Break Preview Or Page Layout view go
 | 'back to normal view, we do this for speed
 | ViewMode = ActiveWindow.View
 | ActiveWindow.View = xlNormalView
 | 'Turn off Page Breaks, we do this for speed
 | .DisplayPageBreaks = False
 | 'Set the first and last row to loop through
 | Firstrow = .UsedRange.Cells(1).Row
 | Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
 | 'We loop from [NAME] to Firstrow (bottom to top)
 | For Lrow = [NAME] To Firstrow Step -1
 | 'We check the values in the A column in this example
 | With .Cells(Lrow, "A")
 | If Not IsError(.Value) Then
 | If .Value = "ron" Then
 | 'This will delete each row with the Value "ron"
 | 'in Column A, case sensitive.
 | If rng Is Nothing Then
 | Set rng = .Cells
 | Else
 | Set rng = Application.Union(rng, .Cells)
 | End If
 | End If
 | End If
 | End With
 | Next Lrow
 | End With
 | 'Delete all rows in one time
 | If Not rng Is Nothing Then rng.EntireRow.Delete
 | ActiveWindow.View = ViewMode
 | With Application
 | .ScreenUpdating = True
 | .Calculation = CalcMode
 | End With
 | End Sub
 | 3: Use AutoFilter to delete rows
 | Tip: with AutoFilter you can also use wildcards.
 | "*ron"  End with ron
 | "ron*"  Start with ron
 | "*ron*" ron is a part of the string
 | You can also use the wildcard ? for a single character.
 | Note: in the examples I use the range .Range("A1:A" & .Rows.Count)
 | Remember that [NAME] of this range is your header cell.
 | One criteria
 | The example below filter [NAME]:A? on the ActiveSheet for the DeleteValue and delete the rows.
 | Sub Delete_with_Autofilter()
 | Dim DeleteValue As String
 | Dim rng As Range
 | Dim calcmode As Long
 | With Application
 | calcmode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'Fill in the value that you want to delete
 | 'Tip: use DeleteValue = "<>ron" to delete rows without ron
 | DeleteValue = "ron"
 | 'Sheet with the data, you can also use Sheets("MySheet")
 | With ActiveSheet
 | 'Firstly, remove the AutoFilter
 | .AutoFilterMode = False
 | 'Apply the filter
 | .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue
 | With .AutoFilter.Range
 | On Error Resume Next
 | Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
 | .SpecialCells(xlCellTypeVisible)
 | On Error GoTo 0
 | If Not rng Is Nothing Then rng.EntireRow.Delete
 | End With
 | 'Remove the AutoFilter
 | .AutoFilterMode = False
 | End With
 | With Application
 | .ScreenUpdating = True
 | .Calculation = calcmode
 | End With
 | End Sub
 | Two criteria
 | The example below filter [NAME]:A? on the ActiveSheet for the DeleteValue1 and 2 and delete the rows.
 | Sub Delete_with_Autofilter_Two_Criteria()
 | Dim DeleteValue1 As [NAME]
 | Dim DeleteValue2 As [NAME]
 | Dim rng As Range
 | Dim calcmode As Long
 | With Application
 | calcmode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'Fill in the two values that you want to delete
 | DeleteValue1 = "ron"
 | DeleteValue2 = "jelle"
 | 'Sheet with the data, you can also use Sheets("MySheet")
 | With ActiveSheet
 | 'Firstly, remove the AutoFilter
 | .AutoFilterMode = False
 | 'Apply the filter
 | .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, _
 | Criteria1:=DeleteValue1, Operator:=xlOr, Criteria2:=DeleteValue2
 | With .AutoFilter.Range
 | On Error Resume Next
 | Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
 | .SpecialCells(xlCellTypeVisible)
 | On Error GoTo 0
 | If Not rng Is Nothing Then rng.EntireRow.Delete
 | End With
 | 'Remove the AutoFilter
 | .AutoFilterMode = False
 | End With
 | With Application
 | .ScreenUpdating = True
 | .Calculation = calcmode
 | End With
 | End Sub
 | Example to filter between two dates, you not use the DeleteValue1 and 2 string then.
 | This will delete all records in 2006. (Note: I use xlAnd here).
 | You can use this also if you want to filter numeric values.
 | .AutoFilter Field:=1, Criteria1:=">=" & DateSerial(2006, 1, 1), _
 | Operator:=xlAnd, Criteria2:="<" & DateSerial(2007, 1, 1) ' yyyy-mm-dd format
 | More then two Criteria
 | For more then two Criteria you can loop through the words in the array.
 | Note: This is also working if you use one word in the array.
 | Sub Delete_with_Autofilter_Array()
 | Dim rng As Range
 | Dim calcmode As Long
 | Dim myArr As [NAME]
 | Dim I As Long
 | With Application
 | calcmode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'Fill in the values that you want to delete
 | myArr = Array("ron", "[NAME]", "Jelle")
 | For I = LBound(myArr) To UBound(myArr)
 | 'Sheet with the data, you can also use Sheets("MySheet")
 | With ActiveSheet
 | 'Firstly, remove the AutoFilter
 | .AutoFilterMode = False
 | 'Apply the filter
 | .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)
 | Set rng = Nothing
 | With .AutoFilter.Range
 | On Error Resume Next
 | Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
 | .SpecialCells(xlCellTypeVisible)
 | On Error GoTo 0
 | If Not rng Is Nothing Then rng.EntireRow.Delete
 | End With
 | 'Remove the AutoFilter
 | .AutoFilterMode = False
 | End With
 | Next I
 | With Application
 | .ScreenUpdating = True
 | .Calculation = calcmode
 | End With
 | End Sub
 | Criteria range on a different sheet
 | Example with the criteria range on a different sheet
 | The example below filter [NAME]:A? In a sheet named “data”.
 | And use as criteria all the cells in column A of a sheet named “Criteria”.
 | Note: You can use also wildcards like *food* or *store if you want.
 | Sub Delete_with_Autofilter_More_Criteria()
 | Dim rng As Range
 | Dim cell As Range
 | Dim CriteriaRng As Range
 | Dim calcmode As Long
 | With Application
 | calcmode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | With Sheets("Criteria")
 | Set CriteriaRng = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
 | End With
 | 'Loop through the cells in the Criteria range
 | For Each cell In CriteriaRng
 | With Sheets("data")
 | 'Firstly, remove the AutoFilter
 | .AutoFilterMode = False
 | 'Apply the filter
 | .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=cell.Value
 | With .AutoFilter.Range
 | Set rng = Nothing
 | On Error Resume Next
 | Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
 | .SpecialCells(xlCellTypeVisible)
 | On Error GoTo 0
 | If Not rng Is Nothing Then rng.EntireRow.Delete
 | End With
 | 'Remove the AutoFilter
 | .AutoFilterMode = False
 | End With
 | Next cell
 | With Application
 | .ScreenUpdating = True
 | .Calculation = calcmode
 | End With
 | End Sub
 | 4: Use Find to delete rows
 | Change this three lines in the macro before you test it.
 | 'We use the ActiveSheet but you can also use Sheets("MySheet")
 | Set sh = ActiveSheet
 | 'We look in column A in this example
 | Set myRng = sh.Range("A:A")
 | 'Add more search strings if you need
 | myStrings = Array("Ron", "[NAME]", "[NAME]")
 | Sub Find_Example()
 | Dim calcmode As Long
 | Dim ViewMode As Long
 | Dim myStrings As [NAME]
 | Dim FoundCell As Range
 | Dim I As Long
 | Dim myRng As Range
 | Dim sh As Worksheet
 | With Application
 | calcmode = .Calculation
 | .Calculation = xlCalculationManual
 | .ScreenUpdating = False
 | End With
 | 'We use the ActiveSheet but you can also use Sheets("MySheet")
 | Set sh = ActiveSheet
 | 'We search in column A in this example
 | Set myRng = sh.Range("A:A")
 | 'Add more search strings if you need
 | myStrings = Array("Ron", "[NAME]", "[NAME]")
 | With sh
 | 'We select the sheet so we can change the window view
 | .Select
 | 'If you are in Page Break Preview Or Page Layout view go
 | 'back to normal view, we do this for speed
 | ViewMode = ActiveWindow.View
 | ActiveWindow.View = xlNormalView
 | 'Turn off Page Breaks, we do this for speed
 | .DisplayPageBreaks = False
 | 'We will search the values in MyRng in this example
 | With myRng
 | For I = LBound(myStrings) To UBound(myStrings)
 | Do
 | Set FoundCell = myRng.Find(What:=myStrings(I), _
 | After:=.Cells(.Cells.Count), _
 | LookIn:=xlFormulas, _
 | LookAt:=xlWhole, _
 | SearchOrder:=xlByRows, _
 | SearchDirection:=xlNext, _
 | MatchCase:=False)
 | 'Use xlPart If you want to search in a part of the FoundCell
 | 'If you use LookIn:=xlValues it will also delete rows with a
 | 'formula that evaluates to "Ron"
 | If FoundCell Is Nothing Then
 | Exit Do
 | Else
 | FoundCell.EntireRow.Delete
 | End If
 | Loop
 | Next I
 | End With
 | End With
 | ActiveWindow.View = ViewMode
 | With Application
 | .ScreenUpdating = True
 | .Calculation = calcmode
 | End With
 | End Sub
 | 5: Use Specialcells to delete rows
 | You can find information on this page.
 | http://www.rondebruin.nl/specialcells.htm