Delete Rows if cell value in multiple columns matches on a different sheet

I am looking to delete rows from Sheet 3 in Multiple columns ( I think I might have to make tables for this ) if say cell data in Columns B,F,I matches cell data from sheet 4 in column B. The VBA I have for a different sheet works but only to match 1 column as it only looks to column B on both sheets.

Sub delete_selected_rows()
Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long

With Worksheets("Inventory Tracker")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
Set rng1 = .Range("B4:B" & lastRow)
End With

Set rng2 = Worksheets("Sheet4").Range("B:B")

For Each c In rng1
If Not IsError(Application.Match(c.Value, rng2, 0)) Then
'if value from rng1 is found in rng2 then remember this cell for deleting
If rngToDel Is Nothing Then
Set rngToDel = c
Else
Set rngToDel = Union(rngToDel, c)
End If
End If
Next c

If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub

Sub deleteentries()

Dim lastrow, i, j As Long
Dim k As Variant

For j = 2 To 30 Step 4
lastrow = Worksheets("Inventory Tracker").Cells(1048576, j).End(xlUp).Row

For i = lastrow To 4 Step -1

k = Application.Match(Worksheets("Inventory Tracker").Cells(i, j).Value, Worksheets("sheet4").Columns(2), 0)

If Not (IsError(k)) Then
Worksheets("Inventory Tracker").Cells(i, j).Delete
Worksheets("Inventory Tracker").Cells(i, j + 1).Delete
Worksheets("Inventory Tracker").Cells(i, j + 2).Delete

Else
End If

Next i
Next j

End Sub

Unfortunately i didn't correctly understand what you want to do. Now this code checks if a serial number from the Inventory Tracker is found anywhere in column B of Sheet 4. If it's found the corresponding data is deleted in sheet Inventory Tracker.

delete entries.xlsm30 KB

OliverScheurich
Gold Contributor
Jun 05, 2023
sebring1983
Sub deleterows() Dim lastrow, i As Long lastrow = Worksheets("Inventory Tracker").Range("B" & Rows.Count).End(xlUp).Row For i = lastrow To 4 Step -1 If Worksheets("Inventory Tracker").Cells(i, 2).Value = Worksheets("sheet4").Cells(i, 2).Value And _ Worksheets("sheet4").Cells(i, 2).Value = Worksheets("sheet4").Cells(i, 6).Value And _ Worksheets("sheet4").Cells(i, 9).Value = Worksheets("sheet4").Cells(i, 6).Value Then Worksheets("Inventory Tracker").Rows(i).Delete Else End If Next i End Sub
You can try this code. It returns the intended result if i correctly understand what you want to do. The test range was 1000 rows with 200 rows to delete.
- sebring1983
  Copper Contributor
  Jun 05, 2023
  So I have Sheet 3 "Inventory Tracker" where I have Serial Number in B, Device Description in C and unit price in D and then same thing on Columns F, G and H and so on, and I realize now I will need to create different Tables for this. This way if what is on Sheet 4 column B matches B,F,J……. it will delete the row for that table. As of right now it looks like I will need to create 8 Tables.
  - OliverScheurich
    Gold Contributor
    Jun 05, 2023
    sebring1983
    So you want to delete cells B10, C10 and D10 of sheet "Inventory Tracker" if cell B10 of this sheet has the same value as cell B10 of Sheet 4?
    
    And you want to delete cells F15, G15 and H15 of sheet "Inventory Tracker" if cell F15 of this sheet has the same value as cell B15 of Sheet 4?
    
    The corresponding result should apply to columns J, K, L and N, O, P and so on in my understanding.

Forum Discussion

Delete Rows if cell value in multiple columns matches on a different sheet

Share

Resources