CODEWITHSUNDEEP
vbaexcel
Larry
Larry

Reputation: 183

Excel macro to compare two ranges for perfect match

When I run a macro, I'd like a message to pop up if the values for L6:L29 match, row to row, the values in M6:M29.

The long and inefficient way to write this might be:

Public Sub MyMacro()
    If Range("L6").Value = Range("M6).Value 
AND Range("L7").Value = Range("M7).Value 
AND Range("L8").Value = Range("M8).Value 
etc.
    Then
        MsgBox "Both columns match!.", vbInformation, "Match!"
    Exit Sub
End If

...

End Sub

But I'm looking for more efficient code that can evaluate each row in the two columns/ranges without having to specify each pair.

I did see the below answer to a similar question, but it evaluates only one row (in this case, #5). I need to evaluate every row in the range: Fastest way to check if two ranges are equal in excel vba

Sub RowCompare()
    Dim ary1() As Variant
    Dim Range1 As Range, Range2 As Range, rr1 As Range, rr2 As Range
    Set Range1 = Range("B9:F20")
    Set Range2 = Range("I16:M27")
    Set rr1 = Range1.Rows(5)
    Set rr2 = Range2.Rows(5)
    ary1 = Application.Transpose(Application.Transpose(rr1))
    ary2 = Application.Transpose(Application.Transpose(rr2))
    st1 = Join(ary1, ",")
    st2 = Join(ary2, ",")
    If st1 = st2 Then
        MsgBox "the same"
    Else
        MsgBox "different"
    End If
End Sub

Upvotes: 0

Views: 602

Answers (1)

user8959429
user8959429

Reputation:

Try this:

Option Explicit

Sub RowCompare()

    Dim i As Long
    Dim ComparisionResult As Boolean

    For i = 6 To 29
        If IIf(Cells(i, 12).Value = "", "", Cells(i, 12).Value) = IIf(Cells(i, 13).Value = "", "", Cells(i, 13).Value) Then
            ComparisionResult = True
        Else
            ComparisionResult = False
            Exit For
        End If
    Next i

   If ComparisionResult Then MsgBox "Both columns match!", vbInformation, "Match!" Else _
        MsgBox "different", vbInformation, "Match!"

End Sub

Upvotes: 2

Related Questions