我有两张具有基本结构的工作表:工作表 (A) 和工作表 (B)。工作表 (B) 是工作表 (A) 的子集,需要将其从工作表 (A) 中删除。如何在 Excel 2007 中完成此操作?
答案1
编辑了评论的代码,使其可以在不同工作表中使用;这将sheet 1 Column A
与以下代码进行比较sheet 2 column A
并删除重复项:sheet 1
Sub removematches()
Dim firstcolumn() As Variant
Dim A As Range
Dim B As Range
Dim i As Long, del As Long
'This will set the ranges to look in.
Set A = Range("A:A")
Set B = Range("Sheet2!A:A")
firstcolumn = A
ReDim Preserve firstcolumn(1 To UBound(firstcolumn), 1 To 2) As Variant
i = 1
del = 0
Do While i <= UBound(firstcolumn)
firstcolumn(i, 2) = Application.WorksheetFunction.CountIf(B, firstcolumn(i, 1))
If firstcolumn(i, 2) > 0 Then
Range("A1").Offset(i - del - 1, 0).Delete Shift:=xlUp
del = del + 1
End If
i = i + 1
Loop
End Sub