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