Fastest way to check if two ranges are equal in excel vba [duplicate]

by

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

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. Getting ScriptControl to work with Excel 2010 x64
  5. Application.Match gives type mismatch
  6. Transpose a range in VBA
  7. Protecting cells in Excel but allow these to be modified by VBA script
  8. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  9. Detect merged cells in VBA Excel with MergeArea
  10. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  11. How do I name a spreadsheet automatically, by referencing two cells?
  12. Excel VBA Delete Rows
  13. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  14. Should I turn .CutCopyMode back on before exiting my sub procedure?
  15. VBA: How to delete filtered rows in Excel?
  16. Determine whether user is adding or deleting rows
  17. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  18. Iterate over slicer via VBA and select a single item each time
  19. Loop through Excel Sheets
  20. Return a range from A1 to the true last used cell
  21. How to add items to a combobox in a form in excel VBA?
  22. How can I open this VBA workbook faster?
  23. How can I automate Save as dialog box in IE11 using VBA?
  24. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  25. Calling regasm without administrative rights for COM interop in Excel VBA
  26. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  27. How do I activate a specific workbook and a specific sheet?
  28. Start VBA macro when editing a cell
  29. VBA Run-time error 438 appears when “paste” runs
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment