Need VBA code to terminate a find loop
 

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

Need VBA code to terminate a find loop
 

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

Need VBA code to terminate a find loop
 

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


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381737