Can't get FindNext after deleting row?

E

Ed

I built a macro step by step to search a worksheet for certain criteria. I
set a range to my search area, then used Find and Find Next to go through
the range and locate the criteria. All worked well - until I came to the
crucial last step: deleting the row with the criteria. When I include the
delete, I get "Unable to get FindNext property of Range class."

I'm thinking I've run afoul of either (a) deleting the row containing my
Range, or (b) because I have deleted a row, I've changed the range set for
my search , and I need to reset it. Either way, I need to get a different
way to do it. Any suggestions are welcome.

Ed

The applicable code is:

' Set search range to 3rd column
Set rngSearch = Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp))

' Search each Cell in column
With rngSearch
Set rngFound = .Find("AAA", LookIn:=xlValues)
If rngFound Is Nothing Then
MsgBox "Not here"
Else:
Address = rngFound.Address

Do
thisRow = rngFound.Row
nextRow = thisRow + 1

' **more code**

Range("C" & thisRow).EntireRow.Delete
Set rngFound = .FindNext(rngFound)

' **ERROR ON NEXT LINE**
Loop While Not rngFound Is Nothing And rngFound.Address <> Address
End If
End With
 
D

Dave Peterson

Since you're deleting things, maybe you could just loop until you don't find
anything:

Option Explicit
Sub testme01()

Dim rngSearch As Range
Dim rngFound As Range

' Set search range to 3rd column
Set rngSearch = .columns(3) 'whole column?

With rngSearch
Do
Set rngFound = .Find("AAA", LookIn:=xlValues)

If rngFound Is Nothing Then
Exit Do
Else
rngFound.EntireRow.Delete
End If
Loop
End With

End Sub
 
Top