Excel VBA对象必需运行时出错

Question

我是VBA的新手，刚刚开始学习。我在这里有一个代码，用于匹配Sheet1和Sheet3中"M"列的所有单元格，并删除Sheet1中包含Sheet3"M"栏中任何值的所有行。如果我使用F8进行检查，我不会得到任何错误，但当我将其分配给按钮时，它在运行时失败，并出现"Object Required"错误。我尝试了一些在网上找到的东西，但似乎都不起作用。下面是我的代码。任何帮助都将不胜感激。

Sub DeleteRows()
Dim rng As Range
Dim rng2 As Range
Dim cell As Object
Dim cell2 As Object
Set rng = Sheets("Sheet1").Range("M2:M1541")
Set rng2 = Sheets("Sheet3").Range("M2:M30")
For Each cell In rng
For Each cell2 In rng2
If cell.Value = cell2.Value Then
cell.EntireRow.Delete
End If
Next
Next
Application.ScreenUpdating = True
End Sub

提前感谢！

Answer 1

您可以循环浏览sheet2中的单元格，并过滤sheet1中的这些项。那么你就不会是双循环了。

Sub Button1_Click()
Dim ws As Worksheet, sh As Worksheet
Dim LstRw As Long, Rng As Range, Frng As Range, c As Range, Nrng As Range
Set ws = Sheets("Sheet2")
Set sh = Sheets("Sheet1")
With ws
LstRw = .Cells(.Rows.Count, "M").End(xlUp).Row
Set Rng = .Range("M2:M" & LstRw)
End With
With sh
Set Frng = .Range("M2:M" & .Cells(.Rows.Count, "M").End(xlUp).Row)
For Each c In Rng.Cells
.Range("M1").AutoFilter Field:=1, Criteria1:=c
On Error Resume Next
Set Nrng = Frng.SpecialCells(xlVisible)
On Error GoTo 0
If Nrng Is Nothing Then
Else
Frng.EntireRow.Delete
End If
Next c
.AutoFilterMode = False
End With
End Sub