Forum Discussion

sebring1983's avatar
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...
excel
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Jun 06, 2023

    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.

sebring1983's avatar
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's avatar
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's avatar
    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

     

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      Jun 05, 2023

      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.

      • sebring1983's avatar
        sebring1983
        Copper Contributor
        Jun 06, 2023

        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

         

         

Resources