If Row 1 = Row 2 then delete Row 2, else MsgBox

[Binning]

Hi there,

I'm trying to input a check into my macro. Basically the logic I'm trying to follow is:

If Row 1 = Row 2 then delete row 2,
ElseIf Row 1 <> Row 2 then Display MsgBox "Row 1 and Row 2 are different. Do you wish to continue?"
If user selects yes, resume
If user selects no, end sub and display MsgBox "Macro ended. Please correct changes manually"

Can anyone help me if this isn't too much trouble?

Thanks,
Binning

 

[Binning]

Sorry I'm not sure about the rules on bumping here but this has now found it's way to page 6 with no reply.

 

[Gavin T]

If Row 1 = Row 2 then...

What do you mean by Row 1 = Row 2?

Do you mean that each cell in Row 1 is equal to the cell in Row 2 that's directly beneath it?

 

[Binning]

Yes Gavin, i.e. A1:AL1 = A2:AL2

 

[Gavin T]

You can try this code:

Sub Match_Rows()
    

    Dim rngCell     As Range
    Dim strResponse As String
    Dim lngCount     As Long

    For Each rngCell In Range("A1:AL1")
        If rngCell <> rngCell.Offset(1, 0) Then
            lngCount = lngCount + 1
        End If
    Next rngCell
    
    If lngCount = 0 Then
        Rows(2).Delete
    Else
        strResponse = MsgBox("Row 1 and Row 2 are different. Do you wish to continue?", vbYesNo)
        If strResponse = vbNo Then
            MsgBox ("Macro ended. Please correct changes manually")
            Exit Sub
        End If
    End If
End Sub

ElseIf Row 1 <> Row 2 then Display MsgBox "Row 1 and Row 2 are different. Do you wish to continue?"
If user selects yes, resume...

When you say resume do you want to have row 2 deleted?

 

[Binning]

Thanks Gav, this is perfect.

Yeh I wanted row 2 deleted

 

[Gavin T]

For Row 2 to be deleted when the user presses yes, use the following code:

Sub Match_Rows()
    

    Dim rngCell     As Range
    Dim strResponse As String
    Dim lngCount     As Long


    For Each rngCell In Range("A1:AL1")
        If rngCell <> rngCell.Offset(1, 0) Then
            lngCount = lngCount + 1
        End If
    Next rngCell
    

    If lngCount <> 0 Then
    strResponse = MsgBox("Row 1 and Row 2 are different. Do you wish to continue?", vbYesNo)
        If strResponse = vbNo Then
            MsgBox ("Macro ended. Please correct changes manually")
            Exit Sub
        End If
    End If
    Rows(2).Delete
End Sub

Good luck!