Need VBA code to terminate a find loop

[mikeburg]

When I import a report & run the following VBA code to delete the headings, after the last heading is deleted, I get:

Runtime Error '91": Object Variable or With block variable not set

What VBA code should be used to terminate the loop to prevent the error?

mikeburg


Sub CDeleteHeadings()
Cells.find(what:="Name & Address", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Do
ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
Cells.findnext(After:=ActiveCell).Activate
Loop Until Cells.findnext = False

[Jim Thomlinson]

You can try modifying your code by changing
Loop Until Cells.findnext = False
to
Loop Until Cells.findnext is nothing
(untested)
or try this code which should be more efficient...

Sub Test
Call DeleteUnwanted("Name & Address")
End Sub

Sub DeleteUnwanted(ByVal DeleteWord As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngCurrent As Range
Dim rngDelete As Range
Dim rngFirst As Range

Set wks = Sheets("Sheet1")
Set rngToSearch = wks.Cells

Set rngCurrent = rngToSearch.Find(DeleteWord)
If Not rngCurrent Is Nothing Then
Set rngDelete = rngCurrent
Set rngFirst = rngCurrent
Do
Set rngDelete = Union(rngCurrent, rngCurrent.Offset(1,0),
rngDelete)
Set rngCurrent = rngToSearch.FindNext(rngCurrent)
Loop Until rngCurrent.Address = rngFirst.Address
rngDelete.EntireRow.Delete
End If
End Sub

--
HTH...

Jim Thomlinson


"mikeburg" wrote:

>
> When I import a report & run the following VBA code to delete the
> headings, after the last heading is deleted, I get:
>
> Runtime Error '91": Object Variable or With block variable not
> set
>
> What VBA code should be used to terminate the loop to prevent the
> error?
>
> mikeburg
>
>
> Sub CDeleteHeadings()
> Cells.find(what:="Name & Address", After:=ActiveCell,
> LookIn:=xlFormulas _
> , LookAt:=xlPart, SearchOrder:=xlByRows,
> SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
> Do
> ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
> ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
> Cells.findnext(After:=ActiveCell).Activate
> Loop Until Cells.findnext = False
>
>
> --
> mikeburg
> ------------------------------------------------------------------------
> mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581
> View this thread: http://www.excelforum.com/showthread...hreadid=381737
>
>

[bhofsetz]

Rearrange some things and test for Is Nothing rather than False and you've got it.

Sub CDeleteHeadings()
Do
Cells.Find(what:="Name & Address", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
ActiveCell.Offset(rowoffset:=0).EntireRow.Delete
Loop Until Cells.FindNext Is Nothing
End Sub

HTH