EXCEL VBA:比较两列并删除重复的行

Question

我正在尝试将 C 列中的列表与从单元格 Y1001 开始的列表进行比较，然后在存在重复项的地方，我想从 C 列中删除重复项的整行。

但是，我的代码没有产生此结果！我的错误在哪里？

谢谢！

Sub Button3_Click()
'run comparison'
'Turn off screen updating to speed up macro'
Application.ScreenUpdating = False
Dim iListCount As Long
Dim x As Variant
Dim iCtr As Long

' Get count of records to search through (list that will be deleted)'
iListCount = Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row
' Loop through the "master" list'
For Each x In Sheets("Sheet1").Range("Y1001:Y" & Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row)
   ' Loop through all records in the second list.
   For iCtr = iListCount To 1 Step -1
      ' Do comparison of next record'
      If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then
         ' If match is true then delete row.
         Sheets("Sheet1").Cells(iCtr, 1).EntireRow.Delete    
       End If
   Next iCtr
Next
Application.ScreenUpdating = True
End Sub

附加信息：

我收到"运行时错误 424：需要对象"

当我运行调试时，错误行是：

If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then

但我没有发现我的错误。

Answer 1

只是一个快速扫描。
此代码：

If x.Value = Sheets("Sheet1").Cells(iCtr, 1).Value Then

将您的Y值与A而不是C进行比较。
应该是这样的。

If x.Value = Sheets("Sheet1").Cells(iCtr, 3).Value Then