Forum Discussion

sebring1983

Copper Contributor

Jun 05, 2023

Solved

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

Jun 06, 2023

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.
  - sebring1983
    Copper Contributor
    Jun 05, 2023
    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