How to compare two entire rows in a sheet
excel, vba
Solution
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 for `Application` to keep the code below easier to read
- `ActiveSheet.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 through `Application.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.
Problem
I am new to VBA. I have job in my hand to improve performance of VBA code. To improve performance of the code, I have to read entire row and compare it with another row. Is there any way to do this in VBA? Pseudocode: ``` sheet1_row1=read row1 from sheet1 sheet2_row1=read row1 from sheet2 if sheet1_row1 = sheet2_row1 then print "Row contains same value" else print "Row contains diff value" end if ```