Forum Discussion
Delete Rows if cell value in multiple columns matches on a different sheet
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 SubUnfortunately 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.
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
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 SubUnfortunately 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.
- That works!! one day I will have the time to learn how to use Macros better.