How to compare two entire rows in a sheet
For your specific example, here are two ways...
Case Insensitive:
MsgBox [and(1:1=2:2)]
Case Sensitive:
MsgBox [and(exact(1:1,2:2))]
...
Below are generalized functions to compare any two contiguous ranges.
Case Insensitive:
Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean
RangesEqual = Evaluate("and(" & r1.Address & "=" & r2.Address & ")")
End Function
Case Sensitive:
Public Function RangesEqual(r1 As Range, r2 As Range) As Boolean
RangesEqual = Evaluate("and(exact(" & r1.Address & "," & r2.Address & "))")
End Function
Sub checkit()
Dim a As Application
Set a = Application
MsgBox Join(a.Transpose(a.Transpose(ActiveSheet.Rows(1).Value)), Chr(0)) = _
Join(a.Transpose(a.Transpose(ActiveSheet.Rows(2).Value)), Chr(0))
End Sub
What's going on:
a
is just shorthand forApplication
to keep the code below easier to readActiveSheet.Rows(1).Value
returns a 2-D array with dimensions (1 to 1, 1 to {number of columns in a worksheet})- We'd like to condense the array above into a single value using
Join()
, so we can compare it with a different array from the second row. However, Join() only works on 1-D arrays, so we run the array twice throughApplication.Transpose()
. Note: if you were comparing columns instead of rows then you'd only need one pass through Transpose(). - Applying
Join()
to the array gives us a single string where the original cell values are separated by a "null character" (Chr(0)
): we select this since it's unlikely to be present in any of the cell values themselves. - After this we now have two regular strings which are easily compared
Note: as pointed out by Reafidy in the comments, Transpose()
can't handle arrays with more than approx. 65,000 elements, so you can't use this approach to compare two whole columns in versions of Excel where sheets have more than this number of rows (i.e. any non-ancient version).
Note 2: this method has quite bad performance compared to a loop used on a variant array of data read from the worksheet. If you're going to do a row-by-row comparison over a large number of rows, then the approach above will be much slower.