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

[sebring1983]

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

[#2]

@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.

[#3]

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.

[#4]

@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.  

[#5]

@OliverScheurich 

Below is a screen shot for better visual reference, if any serial number from Sheet 4 matches a Serial Number on the Inventory Tracker sheet tables it will delete it from the Inventory Tracker. 

Inventory Tracker sheet 

Sheet 4

[#6]

@sebring1983 

Sub deleteentries()

Dim lastrow, i, j As Long

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

For i = lastrow To 4 Step -1

If Worksheets("Inventory Tracker").Cells(i, j).Value = Worksheets("sheet4").Cells(i, 2).Value 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

It should be possible to loop through the tables with this code. In the attached file i've entered sample data in tables 1, 2, 3 and 8 and the matching entries are deleted by the macro.

[#7]

@OliverScheurich 

when I ran this to test it, it did run but only one Item would delete, in the screen shot blow when I run this it will look to sheet 4 Column B but would only remove the first item I have highlighted on the screen shot of the Inventory Tracker sheet.

Inventory Tracker

Sheet 4

[#8 · Accepted solution]

@sebring1983 

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.

[#9]

That works!! one day I will have the time to learn how to use Macros better.