vba - 删除不需要的数据行

Question

Dim mRange As Range

Columns("B:B").Select

i = 0
Set mRange = Range("B:B")
mRange.Find(What:="TRUE", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp

For i = 0 To 1
Columns("B:B").Select
Set mRange = Range("B:B")
mRange.Find(What:="TRUE", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp

Do While Not mRange Is Nothing
Set mRange = Range("B:B")
mRange.Select

mRange.Find(What:="TRUE", After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False).Activate
Rows(ActiveCell.Row).Select
Selection.Delete Shift:=xlUp

Loop

Next i

上面的代码正确地删除了单元格中包含单词“TRUE”的行，但是当它不再找到“TRUE”时收到此错误。它没有跳出循环，而是卡在最后 mRange.Find方法。我做错了什么？谢谢。

“运行时错误 91，对象变量或未设置 block 变量”

Answer 1

正如评论所暗示的，摆脱.Select .

这段代码应该是所需要的。

Do 

   Dim sAdd as String
   sAdd = vbNullString

   Dim rFound as Range
   Set rFound = Range("B:B").Find(What:="TRUE", After:=Cells(Rows.Count,Columns.Count), LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    False)

   If Not rFound is Nothing Then
      sAdd = rFound.Address         
      rFound.EntireRow.Delete Shift:=xlUp
   End If

Loop Until sAdd = vbNullString

如果行集不是非常大，这也将起作用并且可能会更快。

Dim lRow as Long
lRow = Range("B" & Rows.Count).End(xlUp).Row

With Range("B1:B" & lRow)
    .AutoFilter 1, TRUE
    .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter
End With