vba 在excel vba中检查两个范围是否相等的最快方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22270693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fastest way to check if two ranges are equal in excel vba
提问by steinbitur
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.
假设您有两组数据并且行数和列数相同。现在您要检查一组中单元格中的数据是否等于另一组中具有相同相对地址的单元格中的数据。如果对于一行的所有单元格都是如此，请从两组中删除该行。通过比较每个单元格，我可以很容易地对此进行编码，但这对大型数据集不利。请参阅下面两列的代码，其中两组数据碰巧并排在同一张工作表中，它们之间的列偏移量为 300。
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
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
我的实际数据每天有 100 多列和不同数量的列。我正在寻找一种智能、快速的方法来为大型数据集做到这一点。我非常欣赏答案、反馈和批评。:D
回答by Gary's Student
Here is a simple way to compare two rows in isomorphic ranges.............in this example row #5 of each range:
这是在同构范围内比较两行的简单方法…………在此示例中，每个范围的第 5 行：
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
如果您在单元格中嵌入了逗号，则在JOIN 中选择另一个字符
回答by Ron Rosenfeld
If I understand your problem correctly, the following code should allow you to do what you want. Within the code, you select the range you wish to process; the first column of each data set, and the number of columns within each data set.
如果我正确理解你的问题，下面的代码应该允许你做你想做的。在代码中，您选择要处理的范围；每个数据集的第一列，以及每个数据集中的列数。
It does assume only two data sets, as you wrote, although that could be expanded. And there are ways of automatically determining the dataset columns, if there is no other data in between.
正如您所写的，它确实只假设两个数据集，尽管可以扩展。如果中间没有其他数据，还有一些方法可以自动确定数据集列。
Option Explicit
Option Base 0
Sub RemoveDups()
Dim I As Long, J As Long
Dim rRng As Range
Dim vRng As Variant, vRes() As Variant
Dim bRng() As Boolean
Dim aColumns, lColumns As Long
Dim colRowsDelete As Collection
'vRng to include from first to last column to be tested
Set rRng = Range("f1", Cells(Rows.Count, "F").End(xlUp)).Resize(columnsize:=100)
vRng = rRng
ReDim bRng(1 To UBound(vRng))
'columns to be tested
'Specify First column of each data set
aColumns = Array(1, 13)
'num columns in each data set
lColumns = 3
For I = 1 To UBound(vRng)
bRng(I) = vRng(I, aColumns(0)) = vRng(I, aColumns(1))
For J = 1 To lColumns - 1
bRng(I) = bRng(I) And (vRng(I, aColumns(0) + J) = vRng(I, aColumns(1) + J))
Next J
Next I
'Rows to Delete
Set colRowsDelete = New Collection
For I = 1 To UBound(bRng)
If bRng(I) = True Then colRowsDelete.Add Item:=I
Next I
'Delete the rows
If colRowsDelete.Count > 0 Then
Application.ScreenUpdating = False
For I = colRowsDelete.Count To 1 Step -1
rRng.Rows(colRowsDelete.Item(I)).EntireRow.Delete
Next I
End If
Application.ScreenUpdating = True
End Sub
