Thread: Mass Delete Rows with specific phrase

I have a massive amount of data and want to delete all rows that contain "2007" in any cell in that row. Any tips?

washburn321,

Welcome to the forum. You could use a macro. To add a macro to a workbook:

1. Save a copy of the Excel workbook you want to modify
   • Always test macros in a copy so that the original is preserved in case the modifications don't go smoothly
2. Open the copy of the Excel workbook you want to modify
3. Use the keyboard shortcut ALT+F11 to open the Visual Basic Editor
4. Insert -> Module
5. Copy/Paste the code into that area

To run a macro in a workbook:

1. In Excel (not the Visual Basic Editor) press the keyboard shortcut ALT+F8
2. Double-click the desired macro (I named this one MassDeleteMacro_for_washburn321)

Macro code:

Sub MassDeleteMacro_for_washburn321()

    Const Criteria As String = "2007"
    
    Dim RowIndex As Long
    Dim rngFound As Range
    Dim RowsToDelete As Range
    
    For RowIndex = ActiveSheet.UsedRange.Row To ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1
        Set rngFound = Range(RowIndex & ":" & RowIndex).Find(Criteria)
        If Not rngFound Is Nothing Then
            If RowsToDelete Is Nothing Then
                Set RowsToDelete = rngFound
            Else
                Set RowsToDelete = Union(RowsToDelete, rngFound)
            End If
        End If
    Next RowIndex
    
    RowsToDelete.EntireRow.Delete xlShiftUp
    
End Sub

Hope that helps,
~tigeravatar