How to compare two entire rows in a sheet

by 
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.

More Related Contents:

  1. to make this code simpler
  2. VBA code takes long time to execute in excel
  3. Wait until ActiveWorkbook.RefreshAll finishes – VBA
  4. Excel column number from column name
  5. Pop up the Excel Statusbar?
  6. Excel VBA deleting rows in a for loop misses rows
  7. Execute a command in command prompt using excel VBA
  8. What is the lifetime of a global variable in excel vba?
  9. “Unable to get the VLookup property of the WorksheetFunction Class” error [duplicate]
  10. Copy data from another Workbook through VBA
  11. how to detect whether VBA excel found something?
  12. Excel VBA Load worksheet in Function
  13. Searching an excel with two search words (multiple lookup) using vba macro [closed]
  14. Excel VBA – Combine rows with duplicate values in one cell and merge values in other cell
  15. Determine if cell contains data validation
  16. Load csv file into a VBA array rather than Excel Sheet
  17. Insert line break in wrapped cell via code
  18. Change color of certain characters in a cell
  19. Want VBA in excel to read very large CSV and create output file of a small subset of the CSV
  20. VBA Macro crashes after 32000 rows
  21. TextJoin UDF For Excel 2013
  22. Open an Excel file from SharePoint site
  23. How to create dynamic variable names VBA
  24. How to break a long string into multiple lines
  25. Read VBA macros (or vbaProject.bin) of an Excel file without opening it in MS Excel [closed]
  26. simple vba code gives me run time error 91 object variable or with block not set
  27. How to have vba execute every 10 minutes?
  28. Read and write from/to registry in VBA
  29. Excel VBA- Iterate through columns in one workbook, pasting information in corresponding workbook
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment