检查excel vba中两个范围是否相等的最快方法 [英] Fastest way to check if two ranges are equal in excel vba

问题描述

想象一下，您有两组数据，行数和列数相同。现在，您需要检查一组中的单元格中的数据是否等于另一组中相同相对地址的单元格中的数据。如果对于一行的所有单元格都是这样，请从两个集合中删除该行。我可以通过比较每个单元格非常容易地编写，这对于大型数据集是不好的。请参阅下面的代码，其中两组数据恰好在同一张表中并排排列，列间偏移量为300。

Imagine you have two sets of data and the number of rows and columns are the same. Now you want check if data in cells in one set is equal to data in cells with the same relative address in the other set. If thats true for all cells of a row, remove the row from both sets. I can code this very easily by comparing each cell and that's not good for large data sets. See code below for two columns where the two sets of data happen to be in the same sheet side by side with 300 in column offset between them.

Dim RngOb As Range
Dim c As Range

Range("A1", "B1").Select
set RngOb = Range(Selection, Selection.End(xlDown))

For Each c In RngOb.Rows
    If c.Cells(1,1).Value = c.Offset(0, 300).Cells(1,1).Value Then
        If c.Cells(1,2).Value = c.Offset(0, 300).Cells(1,2).Value Then    
            c.EntireRow.Delete
        End If
    End If
Next

我的实际数据有超过100列和每天不同的列数。我正在寻找一个聪明，快速的方式来做这个大数据集。我高度评价答案，反馈和批评。 ：D

My actual data has more than 100 columns and different number of columns from day to day. I'm looking for a smart, fast way to do this for large data sets. I highly appriciate answers, feedback and criticism. :D

推荐答案

这是一个简单的方法来比较同构范围的两行...........在每个范围的这个例子行＃5中：

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

如果在单元格中嵌入了逗号，则在 JOIN

If you have embedded commas in the cells, then choose another character in the JOIN

这篇关于检查excel vba中两个范围是否相等的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！