Delete Excel Rows When Multiple Columns Match Another Sheet with VBA
Question details
The user needs a VBA macro that deletes rows from an inventory worksheet only when values in multiple columns match corresponding records on another worksheet, rather than deleting on a single-column match.
- Product
- WPS Spreadsheets / Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A workbook contains an Inventory Tracker sheet and Sheet4. The user wants to remove rows when the values in columns B, F, and I on the same row match between the two sheets.
- Observed behavior
- The macro should safely compare all required columns, delete only fully matching rows, and process the sheet from bottom to top so no rows are skipped.
Confirm that both worksheets exist, that the key columns are in the same order, and that you have identified which columns must all match, such as B, F, and I. Run the first test on a duplicate workbook so you can close without saving if the macro removes unexpected rows.
Use a bottom-up VBA loop for same-row multi-column matches
Use this solution when the records on both sheets are aligned by row and columns B, F, and I must all match before a row is deleted.
This macro compares the same row number on Inventory Tracker and Sheet4. It loops from the last used row to row 4 because deleting rows shifts lower rows upward; going bottom-up prevents Excel from skipping the row immediately after a deleted row.
Press Alt+F11 in WPS Spreadsheets or Excel to open the Microsoft Visual Basic for Applications editor.
In the Project Explorer, right-click the workbook's VBAProject, choose Insert > Module, and open the new module window.
Paste the following code, then adjust the worksheet names and column letters as needed: Sub DeleteMatchingRows() Dim ws1 As Worksheet, ws2 As Worksheet Dim lastRow As Long, i As Long Set ws1 = Worksheets("Inventory Tracker") Set ws2 = Worksheets("Sheet4") lastRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row For i = lastRow To 4 Step -1 If ws1.Cells(i, "B").Value = ws2.Cells(i, "B").Value _ And ws1.Cells(i, "F").Value = ws2.Cells(i, "F").Value _ And ws1.Cells(i, "I").Value = ws2.Cells(i, "I").Value Then ws1.Rows(i).Delete End If Next i End Sub
Click inside the Sub and press F5, or choose Run > Run Sub/UserForm. Check the result, and save the workbook as a macro-enabled .xlsm file if the deletion is correct.
Run the Multi-Column Row Deletion Macro in WPS Spreadsheets
Use the WPS Spreadsheets VBA editor to run a row-deleting macro that compares columns B, F, and I against Sheet4. WPS opens your existing Excel workbook, preserves the macro structure, and deletes only the rows that meet all criteria.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the workbook that contains Inventory Tracker and Sheet4.
- 2. Open the VBA editor: Press Alt+F11 or go to Developer > Visual Basic to open the VBA editor.
- 3. Insert and paste the macro: Insert a module, paste the multi-column comparison code, and update worksheet names and column letters to match your workbook.
- 4. Run and save: Press F5 to run the macro, verify that the correct rows were deleted, then save as a macro-enabled .xlsm file.
Frequently Asked Questions
Does this macro compare values in the same row or search anywhere in Sheet4?
The recommended code compares the same row number in Inventory Tracker and Sheet4. If you need to search anywhere, you must change the logic to use Find, Match, or a dictionary loop instead of direct Cells(i, "B") comparisons.
How can I change the matching columns or worksheet names?
Open the VBA editor with Alt+F11, find Set ws1 = Worksheets("Inventory Tracker"), Set ws2 = Worksheets("Sheet4"), and the Cells(i, "B"), Cells(i, "F"), and Cells(i, "I") lines. Replace the names or column letters with your own, then run the macro again.
Why does the macro loop from the last row to row 4?
Deleting rows shifts lower rows upward. If you loop top-to-bottom, Excel may skip the row immediately after a deleted row; bottom-up iteration keeps the row positions stable.
What should I do if the macro deletes too many rows?
Close the workbook without saving, reopen the backup copy, and check that all compared cells are in the correct columns and that both sheets use the same row alignment. You can also press F8 to step through the code before running it fully.
Can WPS Spreadsheets run Excel VBA macros?
Yes. WPS Spreadsheets supports VBA-style macros and the VBA editor. Open the editor with Alt+F11, insert a module, and run the macro; save macro-enabled workbooks in .xlsm format.
