Can't get FindNext after deleting row?
 

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

Can't get FindNext after deleting row?
 

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



Ed wrote:

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

--

Dave Peterson

Can't get FindNext after deleting row?
 

Makes sense - which is probably why I didn't think of it! <G Thanks,
Dave.

Ed

"Dave Peterson" wrote in message
...
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



Ed wrote:

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

--

Dave Peterson