Fastest way to check if two ranges are equal in excel vba
excel, vba
Solution
Here is a simple way to compare two rows in isomorphic ranges.............in this example row #5 of each range:
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
If you have embedded commas in the cells, then choose another character in the JOIN
Problem
Imagine you have two sets of data and the number of rows and columns are the same. Now you want check if data in cells in one set is equal to data in cells with the same relative address in the other set. If thats true for all cells of a row, remove the row from both sets. I can code this very easily by comparing each cell and that's not good for large data sets. See code below for two columns where the two sets of data happen to be in the same sheet side by side with 300 in column offset between them. ``` Dim RngOb As Range Dim c As Range Range("A1", "B1").Select set RngOb = Range(Selection, Selection.End(xlDown)) For Each c In RngOb.Rows If c.Cells(1,1).Value = c.Offset(0, 300).Cells(1,1).Value Then If c.Cells(1,2).Value = c.Offset(0, 300).Cells(1,2).Value Then c.EntireRow.Delete End If End If Next ``` My actual data has more than 100 columns and different number of columns from day to day. I'm looking for a smart, fast way to do this for large data sets. I highly appriciate answers, feedback and criticism. :D