How to Delete Excel Rows That Do Not Match a List of Values
Question details
The user needs a way to filter an Excel dataset against an allowed list of values and delete every row where the value is not included in that list.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up a large dataset by discarding records that do not belong to a predefined target list of identifiers.
- Observed behavior
- The user wants to keep only the rows containing the approved values and permanently delete or filter out the non-matching rows from the worksheet.
Always save a backup copy of your workbook before running macros or permanently deleting rows, as these automated actions cannot easily be undone.
Delete Non-Matching Rows Using a VBA Macro
Use a VBA script to iterate through your data from bottom to top and permanently delete any row that does not contain an approved value from your array.
When you need to permanently remove data from your original worksheet, a VBA macro is the most efficient method. It is critical to loop through the rows from bottom to top; otherwise, deleting a row shifts the remaining rows up, causing the loop to skip records.
Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window. Click on 'Insert' in the top menu and select 'Module'.
Paste the following code into the module window: Sub DeleteOtherRecords() Dim rg As Range, arr As Variant, i As Long Set rg = ActiveSheet.Range("A1").CurrentRegion arr = Array("3340", "3341", "3345", "3347", "S5D1", "S5D7") Application.ScreenUpdating = False For i = rg.Rows.Count To 2 Step -1 If UBound(Filter(arr, rg.Cells(i, 3).Value)) = -1 Then rg.Rows(i).Delete Next i Application.ScreenUpdating = True End Sub Update the 'arr = Array(...)' line with your specific allowed values. Change 'rg.Cells(i, 3)' if the values you are checking are not in the 3rd column (Column C).
Press F5 or click the 'Run' button in the toolbar. The macro will scan your data and delete all rows that do not match the values specified in the array.
Extract Matching Rows Using a Dynamic Formula
If you are using Microsoft 365, you can use the FILTER function to extract only the matching rows to a new location instead of destroying the original data.
Manage and Filter Data Efficiently in WPS Spreadsheet
WPS Spreadsheet provides powerful data handling tools, including Advanced Filter and robust formula support, allowing you to easily extract or manage rows that match complex criteria without needing to write code.
- 1. Open Your Data: Launch WPS Spreadsheet and open the workbook containing the dataset you need to clean.
- 2. Set Up an Advanced Filter: Navigate to the 'Data' tab and click on 'Advanced'. This tool allows you to filter your main dataset based on a separate list of criteria.
- 3. Define Your Criteria List: Select your original dataset as the 'List range', and highlight the cells containing your allowed values as the 'Criteria range'. Choose to filter the list in-place or copy the matching results to another location.
- 4. Delete Unwanted Rows: If you filtered the list in-place, you can easily copy the visible (matching) rows to a new sheet, and then delete the old sheet entirely.
Frequently Asked Questions
Why does the VBA macro loop from bottom to top?
When a row is deleted in Excel, the rows below it automatically shift up. If a macro loops from top to bottom, this shifting causes the macro to skip evaluating the row immediately following a deleted row. Looping backward ensures the row indexes remain accurate throughout the process.
Does the FILTER function permanently delete my data?
No. The FILTER function in Microsoft 365 generates a new, dynamic array of data that meets your criteria while leaving the original dataset completely unchanged.
Can I filter by a list without using macros or formulas?
Yes. You can use the 'Advanced Filter' feature found under the Data tab. By setting up your allowed values in a separate criteria range on your sheet, Advanced Filter can instantly hide rows that do not match your list or extract the matching rows to a new location.
