Automating Row Deletion with Complex Criteria in Excel Using VBA

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical

If you’re managing large Excel sheets and need to delete rows that don’t meet specific criteria, manual editing can be time-consuming and error-prone. This article walks you through how to automate this process using VBA (Visual Basic for Applications). We’ll explore a common scenario inspired by forum threads where users need to keep only certain types of data in Column E.

Why People Struggle with Row Deletion

The primary challenge with manually deleting rows based on complex criteria is the potential for human error. Excel has powerful filtering and conditional formatting tools, but they often fall short when dealing with highly specific conditions that require programmatic logic.

Step-by-Step Solution: Automating Row Deletion

We’ll create a VBA macro that will delete rows where Column E doesn’t contain certain criteria. Let’s consider an example: keeping only rows in Column E that contain “E3(“, “E4(” or “Rx(“. Here’s how to do it:

Step 1: Open the VBA Editor

1. Press Alt + F11 to open the VBA editor.
2. In the VBAProject window, find your workbook and double-click on the relevant sheet where you want to apply the macro.
3. Copy and paste the following code into the sheet’s code module:

Sub DeleteNonMatchingRows()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change Sheet1 to your actual sheet name

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    Dim i As Long
    For i = lastRow To 2 Step -1
        If InStr(ws.Cells(i, "E").Value, "E3(") = 0 And _
           InStr(ws.Cells(i, "E").Value, "E4(") = 0 And _
           InStr(ws.Cells(i, "E").Value, "Rx(") = 0 Then
            ws.Rows(i).Delete
        End If
    Next i

    MsgBox "Deletion Complete!", vbInformation
End Sub

Step 2: Understand the Code

The code works as follows:

• Set ws = ThisWorkbook.Sheets(“Sheet1”): Define which worksheet to work on.
• lastRow = ws.Cells(ws.Rows.Count, “E”).End(xlUp).Row: Find the last row with data in Column E.
• The For i = lastRow To 2 Step -1 loop starts from the bottom of the dataset and works its way up to avoid skipping rows during deletion.
• InStr(): Checks if any of the specific criteria (“E3(“, “E4(” or “Rx(“) are found in each cell.
• ws.Rows(i).Delete: Deletes the row if none of the conditions are met.
• MsgBox: Confirms the deletion process is complete with a message box.

Step 3: Run the Macro

1. Save your workbook as a macro-enabled file (macro-enabled workbook, *.xlsm).
2. Return to Excel and press Alt + F8.
3. Select “DeleteNonMatchingRows” from the list and click Run.

Extra Tip: Enhance Criteria with Regular Expressions (Advanced)

If you need to match more complex patterns, consider using regular expressions. Excel’s native VBA doesn’t support RegEx natively, but it can be added via a script:

Sub DeleteRowsWithRegex()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")

    ' Add reference to Microsoft VBScript Regular Expressions library (Tools > References in VBA editor)
    Dim regEx As New RegExp
    regEx.Pattern = "E3\(|E4\(|Rx\("
    regEx.IgnoreCase = True

    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "E").End(xlUp).Row

    Dim i As Long
    For i = lastRow To 2 Step -1
        If Not regEx.Test(ws.Cells(i, "E").Value) Then
            ws.Rows(i).Delete
        End If
    Next i

    MsgBox "Deletion Complete with Regular Expressions!", vbInformation
End Sub

By enabling the Microsoft VBScript Regular Expressions library (Tools > References in VBA editor), you can leverage the power of regex to match complex patterns more efficiently.

Conclusion

Automating row deletion based on complex criteria using VBA is a powerful way to save time and reduce errors. This approach works well for scenarios where specific text patterns need to be matched, preserving only relevant data in your Excel worksheet. For even more advanced Excel tools, check out our comprehensive solutions at Gray Technical.

If you found this tutorial helpful and want to explore other advanced Excel techniques or tools, feel free to reach out. Happy automating!