How to Delete Excel Rows Not Found in Another Worksheet using VBA
Question details
The user needs an Excel VBA macro to preserve rows that match values on another sheet and delete all rows that do not match.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering or cleaning up a dataset by comparing it against a master list on another worksheet using automation.
- Observed behavior
- The current macro deletes matching rows instead of non-matching rows. The goal is to reverse this behavior to keep matches and discard non-matches.
Before running any VBA macro that deletes rows, ensure you have saved a backup copy of your workbook, as macro deletions cannot be reversed using the standard Undo function.
Use a VBA Macro to Group and Delete Non-Matching Rows
Run a VBA script that compares ranges, identifies non-matching cells using an inverse intersection logic, and groups them into a single range variable for efficient deletion.
Instead of deleting rows one by one, the most efficient method is to loop through your source range, check if the cell value exists in the master list, and build a range of all non-matching cells. Once the loop finishes, you delete the entire built range at once.
Press Alt + F11 to launch the Visual Basic for Applications editor in your spreadsheet.
Click on Insert > Module from the top menu to open a blank script window for your new macro.
Write a script that uses a 'FindAll' function to locate matches. Loop through your target range using logic like: For Each Here In Where; If Intersect(All, Here) Is Nothing Then add Here to a 'Source' range variable.
At the end of your script, insert the command 'If Not Source Is Nothing Then Source.EntireRow.Delete' to remove all collected non-matching rows simultaneously.
Press F5 or go to Run > Run Sub/UserForm to execute the script and clean up your worksheet.
Manage and Filter Data Easily in WPS Spreadsheet
WPS Spreadsheet provides seamless compatibility with Excel VBA macros, allowing you to run your row-deletion scripts smoothly. Alternatively, you can use built-in functions combined with filtering to remove non-matching rows without any coding.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing the data you want to clean.
- 2. Create a Helper Column: Insert a new column and use a formula like =VLOOKUP(A2, Sheet2!A:A, 1, FALSE) to check for matches.
- 3. Apply a Filter: Select your header row and navigate to Data > Filter in the top ribbon.
- 4. Filter for Non-Matches: Click the filter dropdown on the helper column and select only the #N/A errors, which represent the rows not found on the other sheet.
- 5. Delete Visible Rows: Highlight the filtered rows, right-click the row numbers, select Delete Row, and then clear the filter to reveal your clean data.
Frequently Asked Questions
Why does my macro run slowly when deleting rows?
Deleting rows one by one forces the spreadsheet to recalculate formulas and redraw the screen repeatedly. Grouping non-matching rows into a single range variable and deleting them all at once is significantly faster.
Can I undo a VBA macro that deleted my rows?
No, actions performed by VBA macros typically cannot be undone using the standard Undo feature (Ctrl+Z). Always create a backup of your file before running macros that alter or delete data.
Is there a way to delete non-matching rows without using VBA?
Yes, you can use functions like VLOOKUP or COUNTIF to identify non-matches. These formulas will return an error or a zero for items not found. You can then apply a standard Data Filter to display only those rows and manually delete them.
