CODEWITHSUNDEEP
vbaexcel
Princess.Bell
Princess.Bell

Reputation: 373

VBA if cells on sheet 1 match cells on sheet 2 - delete row?

I have two worksheets, sheet 1 and sheet 2.

On sheet 1 I have created a form which allows users to enter values into cells B21, B26, I21, I26, P21, P26.

Then by clicking submit, the data gets inserted onto sheet 2 in columns A,B,C,D,E and F on the next available row.

I am also trying to create a macro that once run, will delete the row on sheet 2 where the values match those on sheet1.

At the moment i am keeping it simple by using an IF statement, but this gives me a type mismatch error.

Sub QuickCull()

If Sheets(1).Range("B21").Value = Sheets(2).Range("A:A").Value And _
    Sheets(1).Range("B26").Value = Sheets(2).Range("B:B").Value And _
    Sheets(1).Range("P21").Value = Sheets(2).Range("C:C").Value And _ 
    Sheets(1).Range("I21").Value = Sheets(2).Range("D:D").Value And _
    Sheets(1).Range("I26").Value = Sheets(2).Range("E:E").Value And _
    Sheets(1).Range("P26").Value = Sheets(2).Range("F:F").Value Then

    Rows(ActiveCell.Row).EntireRow.Delete
End If

End Sub

Please can someone show me where i am going wrong?

Upvotes: 0

Views: 2062

Answers (1)

Shai Rado
Shai Rado

Reputation: 33672

First, you need to see if the first condition is met, so we will look for the value in Range("B21") through the entire Column A in Sheets(2) using the Match function. If it return a successful match, we will then use that result (RowMatch representing a row number).

Second, we need to check that all other If have matching values in RowMatch in Sheets(2). If it does, then we can delete that row in Sheets(2).

Try the code below:

Option Explicit

Sub QuickCull()

Dim RowMatch As Long

With Sheets(2)
    If Not IsError(Application.Match(Sheets(1).Range("B21").Value, .Range("A:A"), 0)) Then
        RowMatch = Application.Match(Sheets(1).Range("B21").Value, .Range("A:A"), 0)

        If Sheets(1).Range("B26").Value = .Range("B" & RowMatch).Value And _
            Sheets(1).Range("P21").Value = .Range("C" & RowMatch).Value And _
            Sheets(1).Range("I21").Value = .Range("D" & RowMatch).Value And _
            Sheets(1).Range("I26").Value = .Range("E" & RowMatch).Value And _
            Sheets(1).Range("P26").Value = .Range("F" & RowMatch).Value Then

            .Rows(RowMatch).Delete
        End If

    End If
End With

End Sub

Upvotes: 1

Related Questions