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 
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 readActiveSheet.Rows(1).Value returns a 2-D array with dimensions (1 to 1, 1 to {number of columns in a worksheet})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().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.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.
Chr(0) is the NULL character. It is usually used as a delimiter in strings. However, it is much better to use the inbuilt constant vbNullChar instead of Chr(0) as it doesn't equire runtime evaluation.
Join requires working with a 1-D array: the code is getting a 2-D array from the row, so two passes through Transpose converts it to 1-D. Chr(0) is used as a joining character because it's very unlikely to be in any of the cells on your 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 
[ x ] notation, it's the same as Evaluate(" x ")
OK, this ought to be fairly fast: minimal interaction between Excel UI and VBA (which is where much of the slowness lives). Assumes worksheets have similar layouts from $A$1 and that we're only going to attempt to match the common area of the UsedRanges for the two sheets:
Public Sub CompareSheets(wks1 As Worksheet, wks2 As Worksheet) Dim rowsToCompare As Long, colsToCompare As Long rowsToCompare = CheckCount(wks1.UsedRange.Rows.Count, wks2.UsedRange.Rows.Count, "Row") colsToCompare = CheckCount(wks1.UsedRange.Columns.Count, wks2.UsedRange.Columns.Count, "Column") CompareRows wks1, wks2, rowsToCompare, colsToCompare End Sub Private Function CheckCount(count1 As Long, count2 As Long, which As String) As Long If count1 <> count2 Then Debug.Print "UsedRange " & which & " counts differ: " _ & count1 & " <> " & count2 End If CheckCount = count2 If count1 < count2 Then CheckCount = count1 End If End Function Private Sub CompareRows(wks1 As Worksheet, wks2 As Worksheet, rowCount As Long, colCount As Long) Debug.Print "Comparing first " & rowCount & " rows & " & colCount & " columns..." Dim arr1, arr2 arr1 = wks1.Cells(1, 1).Resize(rowCount, colCount).Value arr2 = wks2.Cells(1, 1).Resize(rowCount, colCount).Value Dim rIdx As Long, cIdx As Long For rIdx = LBound(arr1, 1) To UBound(arr1, 1) For cIdx = LBound(arr1, 2) To UBound(arr1, 2) If arr1(rIdx, cIdx) <> arr2(rIdx, cIdx) Then Debug.Print "(" & rIdx & "," & cIdx & "): " & arr1(rIdx, cIdx) & " <> " & arr2(rIdx, cIdx) End If Next Next End Sub 
Excel 2016 has a built in function called TEXTJOIN
https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c
Looking at @Tim Williams answer and using this new function (which does not have the 65536 row limit):
Sub checkit() MsgBox WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(1).Value) = _ WorksheetFunction.TextJoin(Chr(0), False, ActiveSheet.Rows(2).Value) End Sub Written as a function:
Public Function CheckRangeValsEqual(ByVal r1 As Range, ByVal r2 As Range, Optional ByVal strJoinOn As String = vbNullString) As Boolean CheckRangeValsEqual = WorksheetFunction.TextJoin(strJoinOn, False, r1.Value) = _ WorksheetFunction.TextJoin(strJoinOn, False, r2.Value) End Function 
Match = True Row1length = Worksheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column Row2length = Worksheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column If Row1length <> Row2length Then 'Not equal Match = False Else For i = 1 To Row1length If Worksheets("Sheet1").Cells(1, i),Value <> Worksheets("Sheet2").Cells(1, i) Then Match = False Exit For End If Next End If If Match = True Then Debug.Print "match" Else Debug.Print "not match" End If 
If Worksheets("Sheet1").Cells(1, i),Value <> Worksheets("Sheet2").Cells(1, i) Then A typo there?
Here's a bit of code that will do two vector ranges. You can run it against two rows, two columns.
Don't think it's as fast as the x2 transpose method, but it's more flexible. The column invocation takes a bit longer since there are 1M items to compare!
Option Explicit Public Sub Test() 'Check two columns Debug.Print DataAreasAreSame(Columns("a"), Columns("b")) 'Check two rows Debug.Print DataAreasAreSame(Rows(1), Rows(2)) End Sub Public Function DataAreasAreSame(ByVal DataArea1 As Range, ByVal DataArea2 As Range) As Boolean Dim sFormula As String sFormula = "=SUM(If(EXACT(" & DataArea1.Address & "," & DataArea2.Address & ")=TRUE,0,1))" If Application.Evaluate(sFormula) = 0 Then DataAreasAreSame = True End Function =EXACT(B2;D2) formula and drag down, best option for me.
I'll put in a sledgehammer-to-crack-a-nut answer here, for completeness, because the question 'Are these two ranges identical?' is turning up as an unexamined component of everyone else's 'compare my ranges and then do this complicated thing...' questions.
Your question is a simple question about small ranges. My answer is for large ones; but the question is a good one, and a good place for a more general answer, because it's simple and clear: and 'Do these ranges differ?' and 'Has someone tampered with my data?' are relevant to most commercial Excel users.
Most of the answers to the typical 'compare my rows' questions are cell-by-cell reads and comparisons in VBA. The simplicity of these answers is commendable, but this approach performs very slowly on a large data sets because:
var = Range("A1") as it does to pick up the entire range in one go using var = Range("A1:Z1024")... ...And every interaction with the sheet takes four times as much time as a string comparison in VBA, and twenty times longer than an comparison between floating-point decimals; and that, in turn, is three times longer than an integer comparison.
So your code will probably be four times faster, and possibly a hundred times faster, if you read the entire range in one go, and work on the Range.Value2 array in VBA.
That's in Office 2010 and 2013 (I tested them); for older version of Excel, you'll see quoted times between 1/50th and 1/500th of a second, for each VBA interaction with a cell or range of cells. That'll be way slower because, in both old and new versions of Excel, the VBA actions will still be in single-digit numbers of microseconds: your code will run at least a hundred times faster, and probably thousands of times faster, if you avoid cell-by-cell reads from the sheet in older versions of Excel.
arr1 = Range1.Values arr2 = Range2.Values
' Consider checking that the two ranges are the same size ' And definitely check that they aren't single-cell ranges, ' which return a scalar variable, not an array, from .Value2
' WARNING: THIS CODE WILL FAIL IF YOUR RANGE CONTAINS AN ERROR VALUE
For i = LBound(arr1, 1) To Ubound(arr1, 2)
For j = LBound(arr1, 2) To Ubound(arr1, 2)
If arr1(i, j) <> arr2(i, j) Then bMatchFail = True Exit For End If
Next j
If bMatchFail Then Exit For
Next i
Erase arr1 Erase arr2
You'll notice that this code sample is generic, for two ranges of the same size taken from anywhere - even from separate workbooks. If you're comparing two adjacent columns, loading a single array of two columns and comparing IF arrX(i, 1) <> arrX(i,2) Then is going to halve the runtime.
Your next challenge is only relevant if you're picking up tens of thousands of values from large ranges: there's no performance gain in this extended answer for anything smaller than that.
What we're doing is:
The idea is very simple, although the underlying mathematics is quite challenging for non-mathematicians: rather than comparing one value at a time, we run a mathematical function that 'hashes' the values into a short identifier for easy comparison.
If you're repeatedly comparing ranges against a 'reference' copy, you can store the 'reference' hash, and this halves the workload.
There are some fast and reliable hashing functions out there, and they are available in Windows as part of the security and cryptography API. There is a slight problem in that they run on strings, and we have an array to work on; but you can easily find a fast 'Join2D' function that gets a string from the 2D arrays returned by a range's .Value2 property.
So a fast comparison function for two large ranges will look like this: Public Function RangeCompare(Range1 as Excel.Range, Range2 As Excel.Range) AS Boolean ' Returns TRUE if the ranges are identical. ' This function is case-sensitive. ' For ranges with fewer than ~1000 cells, cell-by-cell comparison is faster
' WARNING: This function will fail if your range contains error values.
RangeCompare = False
If Range1.Cells.Count <> Range2.Cells.Count Then RangeCompare = False ElseIf Range1.Cells.Count = 1 then RangeCompare = Range1.Value2 = Range2.Value2 Else RangeCompare = MD5(Join2D(Range1.Value2)) = MD5(Join2D(Range2.Value2)) Endif
End Function
I've wrapped the Windows System.Security MD5 hash in this VBA function: Public Function MD5(arrBytes() As Byte) As String ' Return an MD5 hash for any string
' Author: Nigel Heffernan Excellerando.Blogspot.com
' Note the type pun: you can pass in a string, there's no type conversion or cast ' because a string is stored as a Byte array and VBA recognises this.
oMD5 As Object 'Set a reference to mscorlib 4.0 to use early binding
Dim HashBytes() As Byte Dim i As Integer
Set oMD5 = CreateObject("System.Security.Cryptography.MD5CryptoServiceProvider") HashBytes = oMD5.ComputeHash_2((arrBytes))
For i = LBound(HashBytes) To UBound(HashBytes) MD5 = MD5 & Right("00" & Hex(HashBytes(i)), 2) Next i
Set oMD5 = Nothing ' if you're doing this repeatedly, declare at module level and persist Erase HashBytes
End Function
A fast and simple Join2D function was posted by Dick Kusleika on Daily Dose of Excel in 2015:
Public Function Join2D(ByVal vArray As Variant, Optional ByVal sWordDelim As String = " ", Optional ByVal sLineDelim As String = vbNewLine) As String Dim i As Long, j As Long Dim aReturn() As String Dim aLine() As String ReDim aReturn(LBound(vArray, 1) To UBound(vArray, 1)) ReDim aLine(LBound(vArray, 2) To UBound(vArray, 2)) For i = LBound(vArray, 1) To UBound(vArray, 1) For j = LBound(vArray, 2) To UBound(vArray, 2) 'Put the current line into a 1d array aLine(j) = vArray(i, j) Next j 'Join the current line into a 1d array aReturn(i) = Join(aLine, sWordDelim) Next i Join2D = Join(aReturn, sLineDelim) End Function If you need to excise blank rows before you make the comparison, you'll need the Join2D function I posted in StackOverflow back in 2012.
The most common application of this type of hash comparison is for spreadsheet control - change monitoring - and you'll see Range1.Formula used instead of Range1.Value2: but your question is about comparing values, not formulae.
Footnote: I've posted a very similar answer elsewhere. I'd've posted it here first if I'd seen this question earlier.
If you want to do this in MS excel, you can do the following.
For example, you have column range of each row from "A" to "F" and have to compare between Row 2 and Row 3. To check entire row and compare it with another row we can specify this in formula in a new Result column and instead of pressing Enter after typing the formula, press Ctrl + Shift + Enter.
=AND(EXACT(A2:F2,A3:F3)) The result will be TRUE if they match and FALSE if they don't. You'll see curly braces around your formula if you've correctly entered it as an array formula. After this, drag down every row so that each cell of this Result Column will have comparison result between this row and the following!
I know there are already answers here, but here is a simple VBA-only function that compares the values in any two ranges, returning TRUE if they match, or the first non-matching item number if they don't. (It returns FALSE if the ranges do not have the same number of cells.)
Function RangesEqualItemNo(Range1 As Range, Range2 As Range) As Variant Dim CellCount As Long If Range1.Count = Range2.Count Then For CellCount = 1 To Range1.Cells.Count If Range1.Cells.item(CellCount).Value <> Range2.Cells.item(CellCount).Value Then RangesEqualItemNo = CellCount Exit Function End If Next CellCount RangesEqualItemNo = True Else RangesEqualItemNo = False End If End Function Or as a simple boolean function:
Function RangesEqual(Range1 As Range, Range2 As Range) As Boolean Dim CellCount As Long If Range1.Count = Range2.Count Then For CellCount = 1 To Range1.Cells.Count If Range1.Cells.item(CellCount).Value <> Range2.Cells.item(CellCount).Value Then RangesEqual = False Exit Function End If Next CellCount RangesEqual = True Else RangesEqual = False End If End Function Although this may not be fancy, this sort of brute-force approach is often the fastest.
This compares values, so it will automatically transpose between columns and rows, which may or may not be what you want.
To take this to the logical next step, the following function will return an array of each item number that is different.
Function RangeDiffItems(Range1 As Range, Range2 As Range, Optional DiffSizes As Boolean = False) As Long() Dim CellCount As Long Dim DiffItems() As Long Dim DiffCount As Long ReDim DiffItems(1 To Range1.Count) DiffCount = 0 If Range1.Count = Range2.Count Or DiffSizes Then For CellCount = 1 To Range1.Cells.Count If Range1.Cells.item(CellCount).Value <> Range2.Cells.item(CellCount).Value Then DiffCount = DiffCount + 1 DiffItems(DiffCount) = CellCount End If Next CellCount If DiffCount = 0 Then DiffItems(1) = 0 Else DiffItems(1) = -1 End If If DiffCount = 0 Then ReDim Preserve DiffItems(1 To 1) Else ReDim Preserve DiffItems(1 To DiffCount) RangeDiffItems = DiffItems End Function If there are no differences, it returns a 0 in the first array slot, or if the arrays are of different sizes, it returns a -1 for the first array spot. To allow it to compare arrays of different sizes, optionally enter TRUE for the third parameter.
There are also a few more answers to this question elsewhere.
In my version, I do not declare anything (Dim). It might be wrong but for all the years I have used Excel VBA - mostly it never stopped the code from working. In this case, the range is given as an area. If not, then you should use Dim Range1 As Range, etc. In my code, the area must be the same witch it mostly is anyway, the same rows and columns. This here is in my opinion the shortest way:
Set Range1 = Range("A1:B5") Set Range2 = Range("D1:E5") 'Range1 For Each rng In Range1 'Selection st1 = st1 & rng & "," Next rng 'Range2 For Each rng In Range2 'Selection st2 = st2 & rng & "," Next rng 'compare If st1 = st2 Then MsgBox "the same" Else MsgBox "different" End If This other example is a bit longer, it reads the rows and columns and uses this for both areas. If you set a range then range(1) will still be the first cell in this range from the top. Here is my code:
Sub COMPARE() Set Range1 = Range("A1:B5") Set Range2 = Range("D1:E5") 'area coly = Range1.Columns.Count 'columns rowy = Range1.Rows.Count 'rows For i = 1 To coly For j = 1 To rowy st1 = st1 & Range1(i, j) st2 = st2 & Range2(i, j) Next j Next i 'compare If st1 = st2 Then MsgBox "the same" Else MsgBox "different" End If End Sub 
Variant (which has a performance overhead).Option Explicit enabled), the compiler will scream at you as soon as you attempt to compile the code. Think about how much time that could save you.
Variant array and loop the array, as has been already demonstrated in this thread.
Option Explicit is a terrible recommendation. In my opinion, this answer adds nothing of value and should be deleted.