删除不需要的数据行 [英] Deleting Rows of Data not Needed

问题描述

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 方法上.我做错了什么?谢谢.

The above code correctly deletes out the rows where a cell has the word "TRUE" in it, but receives this error when it can no longer find "TRUE". It does not jump out of the loop, but hangs at the final mRange.Find method. What have I done wrong? Thx.

运行时错误91，对象变量或未设置块变量"

"Run-time error 91, Object variable or With block variable not set"

推荐答案

正如注释所建议的，摆脱.选择.

As the comments suggest, get rid of .Select.

此代码应为所需的所有内容.

This code should be all that's needed.

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

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

This will also work and may be faster if the rowset isn't extremely large.

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

这篇关于删除不需要的数据行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！