Deleting Rows of Data not Needed

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

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.

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

That's what happens in a `FIND` when it cannot find what you are looking for. I would suggest creating a loop that goes from the last row to the top, looks for "TRUE" in the appropriate column and then delete the row like that. — Kyle, Jun 29 '16 at 16:32
Also, it's highly suggested to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Jun 29 '16 at 16:38

score 3 · Accepted Answer · answered Jun 29 '16 at 16:45

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

Thank you very much @Scott Holtzman. I opted for your second idea and it worked beautifully! What a neat idea! — lindqmx1, Jun 29 '16 at 17:10

score 0 · Answer 2 · answered Jun 29 '16 at 16:51

Application.ScreenUpdating = False
Dim rFound As Range
Dim mRange As Range

Set mRange = Range("B:B")

Do

    Set rFound = mRange.Find(What:="TRUE", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
    If Not rFound Is Nothing Then rFound.EntireRow.Delete

Loop Until rFound Is Nothing

Application.ScreenUpdating = True

Deleting Rows of Data not Needed

2 Answers2

Linked