How to Delete Excel Rows Based on Two Column Matches Using VBA
Question details
The user needs to delete rows in a spreadsheet if matching text is found simultaneously in columns D and E.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up a dataset by removing specific rows that contain a precise combination of user-specified words in two designated columns.
- Observed behavior
- The process must be automated to start searching from row 6 down to the last used row, ensure the search ignores text capitalization, and successfully delete rows without skipping any due to index shifting.
Ensure you save a backup copy of your workbook before running any VBA macros, as row deletions executed via macros cannot typically be reversed using the standard Undo feature.
Use a VBA Macro to Loop Backward and Delete Rows
Run a custom VBA script that prompts for two keywords, scans from the bottom up to row 6, and deletes rows where both keywords appear in columns D and E.
When deleting rows conditionally in VBA, it is crucial to loop backward (from the last row up to the starting row). If you loop forward, deleting a row shifts the remaining rows up, causing the macro to skip evaluating the next row.
To make the search case-insensitive without altering the actual cell data, the VBA script can utilize the `UCase` function to standardize both the cell text and the search string during the comparison.
Press `ALT + F11` to open the Visual Basic for Applications (VBA) editor in your spreadsheet application.
Click `Insert` on the top menu bar, then select `Module` to create a blank window for your code.
Define your variables for the search terms and the last row. Use `LastRow = Cells(Rows.Count, "D").End(xlUp).Row` to find the bottom of your data. Create a backward loop using `For i = LastRow To 6 Step -1`.
Inside the loop, use an `If` statement: `If UCase(Cells(i, "D").Value) = UCase(Word1) And UCase(Cells(i, "E").Value) = UCase(Word2) Then Rows(i).Delete`. Close the loop with `Next i`.
Press `F5` or click the green `Run` button on the toolbar to execute the macro. Provide the search terms if your script uses `InputBox` prompts.
Filter and Delete Rows Smoothly in WPS Spreadsheet
While VBA provides excellent automation, WPS Spreadsheet also allows you to perform complex multi-column deletions quickly using advanced filtering tools—no coding required. WPS Office offers robust compatibility with Microsoft Excel files, including macro-enabled workbooks (.xlsm).
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing the data you wish to clean.
- 2. Apply AutoFilter: Navigate to the `Data` tab on the ribbon and click the `AutoFilter` button.
- 3. Set Multi-Column Criteria: Click the filter dropdowns for columns D and E. Select `Text Filters` > `Equals` and input your target words. This will hide all non-matching rows.
- 4. Delete the Filtered Rows: Highlight the visible rows containing the matched data (starting from row 6), right-click the row numbers on the left, and choose `Delete Rows`.
- 5. Clear the Filter: Click `AutoFilter` again to turn off the filters. Your dataset is now clean and retains only the required data.
Frequently Asked Questions
Why must the VBA loop run backward when deleting rows?
If you loop forwards (e.g., from row 6 to 100) and delete row 6, the old row 7 shifts up to become the new row 6. The loop then moves to row 7, effectively skipping the row that just shifted up. Looping backward with 'Step -1' ensures that shifting rows do not affect the index of the rows yet to be evaluated.
How do I make the VBA search strictly case-insensitive?
You can convert both the cell value and the search string to uppercase using the `UCase()` function in VBA before comparing them. Alternatively, you can type `Option Compare Text` at the very top of your VBA module, which makes all string comparisons in that module case-insensitive automatically.
Can I perform this multi-column deletion without using VBA macros?
Yes. You can use the standard Filter tool. Apply filters to columns D and E to show only the rows matching your criteria. Once filtered, highlight the visible rows, right-click the row headers, and select 'Delete Row'. Finally, turn off the filter.
How do I dynamically find the last used row in VBA?
To find the last used row dynamically in column D, you can use the VBA snippet: `LastRow = Cells(Rows.Count, "D").End(xlUp).Row`. This acts like jumping to the very bottom of the sheet and pressing Ctrl+Up Arrow to find the last cell with data.
