If Column A = Value & Column B = Value in Same Row THEN Delete Row

[perdooky]

Hello Everyone,

I'm looking to search all rows within a sheet for one value in Column A, and then when it finds that value, identify the rows that have a second value in Column B as well. Once that is found, delete the row.

For example,

A B
22222 111
22222 222
22222 333

Find the row that has 22222 is column A & 222 in column B, and delete this row. If you can please help me make the code using this 22222 222 combo as an example, I can edit the numbers for a few exceptions I need it for.

Thanks!

[Leith Ross]

Hello perdooky,

This macro will delete any row whose value in cell "A" matches 22222 and the adjacent cell in column "B" is part of the cell in "A".

Sub DeleteRows()

    Dim Cell As Range
    Dim EndRow As Long
    Dim Match As String
    Dim Rng As Range
    Dim SubMatch As String
    Dim Wks As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        
        Match = 22222
        SubMatch = 222
        
            EndRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
            If EndRow < Rng.Row Then Exit Sub
            
            Set Rng = Rng.Resize(RowSize:=EndRow - Rng.Row + 1)
            
            Application.ScreenUpdating = False
            
                For Each Cell In Rng
                    If Cell = Match Then
                       SubMatch = Cell.Offset(0, 1)
                       If Match Like "*" & SubMatch & "*" Then
                          Cell.EntireRow.Delete
                       End If
                    End If
                Next Cell
     
            Application.ScreenUpdating = True
            
End Sub

[perdooky]

It looks like this macro is not working for the actual numbers I need to use it for. I should have just told from the beginning, I need this to run through a spreadsheet of data for these three combinations in Column A & B:

10083 & 4800
10346 & 4800
10153 & 4800

Thanks for your help already. It looks like this is almost what I need, but I may have messed up by telling you the data in column B was 3 digits. It will always be a 5 digit number in column A and 4 digit number in column B. Thanks for your help so far!

[tigeravatar]

So, if column A = 10083 or 10346 or 10153 and column B = 4800 then delete the row?

[martindwilson]

just use advanced filter and delete the rows found click data/filter /advanced to see criteria, saves all this macro malarkey! or for multiple see advanced mult

[perdooky]

Tiger, correct. But I would hopefully want the solution to be open to different numbers in column B other than 4800.

Martin, correct me if I'm wrong, but isn't that filter doing the opposite of what I want? I need to delete only those three combinations, not delete everything but.

Thanks everyone.

[martindwilson]

hmm i cant see how,if they show up just delete the rest will be left intact

[perdooky]

Still looking for a solution, thanks.

[vthokie2010]

I think you need to elaborate on exactly the criteria for a delete if you cannot adjust the code provided above. If it is constantly changing criteria you may want to name a range and keep that up to date and use that as the criteria for a delete. Unsure until I have some more information.

[perdooky]

I think you need to elaborate on exactly the criteria for a delete if you cannot adjust the code provided above. If it is constantly changing criteria you may want to name a range and keep that up to date and use that as the criteria for a delete. Unsure until I have some more information.

If you read above I mention that the code provided does not work with the actual 3 combinations I need that will be in Column A & B (so I can't adjust the code):

10083 & 4800
10346 & 4800
10153 & 4800

I need it to search columns A & B, and delete the lines that match that criteria.

I took the code, replaced 22222 & 222 with 10083 & 4800, and it doesn't delete the line. I'd be curious to find out why it works with one set of numbers and not another.

[vthokie2010]

Here is your new code. The problem had to do with starting from row 1 as opposed to the last row. Since you might delete rows during the for each block it skips cells because of how it changes the row number after the delete.

Sub DeleteRows()

    Dim i As double
    Dim EndRow As Long
    Dim Rng As Range
    Dim Wks As Worksheet
    
        Set Wks = ActiveSheet
        
        Set Rng = Wks.Range("A1")
        
               
            EndRow = Wks.Cells(Rows.Count, Rng.Column).End(xlUp).Row
            If EndRow < Rng.Row Then Exit Sub
            
            Set Rng = Rng.Resize(RowSize:=EndRow - Rng.Row + 1)
            
            Application.ScreenUpdating = False
            
                For i = EndRow To 1 Step -1
                    If Cells(i, 1).Value = 10083 Or 10346 Or 10153 Then
                        If Cells(i, 2).Value = 4800 Then
                            Cells(i, 1).EntireRow.Delete
                        End If
                    End If
                Next i
     
            Application.ScreenUpdating = True
            
End Sub

[perdooky]

Thanks for the tweak, this work great!