VBA 比较类似的列表

Question

>我有 2 张列表非常相似的工作表。例

Column A   Column B
1          1
2          3
3          3.5
4          4
4.5        5
5

我应该将缺少的元素添加到 B，然后从 B 中删除多余的元素.我不是在寻找复制粘贴，这个数字实际上是链接，我将在复制和删除之前运行宏。

所以

Call add(2)
Call add(4.5)
Call remove(3.5)

有没有比去更快的方法

for (all elements in A)
for (all elements in B)
if (they match) then mark as ok
delete all non marked in B
add all non marked in A

感觉很慢，我不知道如何"标记">

Answer 1

Option Explicit
Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim iRow As Long
Dim iCol As Long
strRangeToCheck = "A1:IV65536"
' If you know the data will only be in a smaller range, reduce the size of the ranges above.
Debug.Print Now
varSheetA = Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = Worksheets("Sheet2").Range(strRangeToCheck) ' or whatever your other sheet is.
Debug.Print Now
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
' Cells are different.
' Code goes here for whatever it is you want to do.
End If
Next iCol
Next iRow
End Sub

尝试使用此设置，在 else 语句中，您需要创建一个打印字符串，以便它可以说明此值是否与此匹配