Loop Macro
 

I created a simple Macro to Find and then delete certain rows.

Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

End Sub

I them wanted to make this macro loop through the column until all instances
of Page were deleted. So I added Do Until ActiveCell.Value = " " at the
beginning and Loop before the End Sub. It removed all the rows but a run-time
error '91' appeared that stated Object variable or with block variable not
set. How do I correct my loop statement to run correctly?

Loop Macro
 

Mikey;551139 Wrote:
I created a simple Macro to Find and then delete certain rows.

Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt
_
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

End Sub

I them wanted to make this macro loop through the column until all
instances
of Page were deleted. So I added Do Until ActiveCell.Value = " " at
the
beginning and Loop before the End Sub. It removed all the rows but a
run-time
error '91' appeared that stated Object variable or with block variable
not
set. How do I correct my loop statement to run correctly?


Do
Set xx = Cells.Find(What:="Page", LookIn:=xlFormulas, LookAt:=xlPart,
MatchCase:=False, SearchFormat:=False)
If Not xx Is Nothing Then xx.EntireRow.Delete
Loop Until xx Is Nothing
Note that this code and yours does't restrict itself to
just searching a column, but the whole sheet.


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151853

Microsoft Office Help

Loop Macro
 

I'd try:

Dim FoundCell as range
dim wks as worksheet

set wks = worksheets("Sheet1")

do
with wks.range("a1").entirecolumn 'What column???
set foundcell = .cells.find(what:="Page", _
after:=.cells(.cells.count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end with

if foundcell is nothing then
exit do
else
foundcell.entirerow.delete
end if
Loop


Mikey wrote:

I created a simple Macro to Find and then delete certain rows.

Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

End Sub

I them wanted to make this macro loop through the column until all instances
of Page were deleted. So I added Do Until ActiveCell.Value = " " at the
beginning and Loop before the End Sub. It removed all the rows but a run-time
error '91' appeared that stated Object variable or with block variable not
set. How do I correct my loop statement to run correctly?

--

Dave Peterson

Loop Macro
 

Ignore Post - I found the answer in another post about finding and deleting
rows

"Mikey" wrote:

I created a simple Macro to Find and then delete certain rows.

Cells.Find(What:="Page", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveCell.Rows("1:1").EntireRow.Select
Selection.Delete Shift:=xlUp

End Sub

I them wanted to make this macro loop through the column until all instances
of Page were deleted. So I added Do Until ActiveCell.Value = " " at the
beginning and Loop before the End Sub. It removed all the rows but a run-time
error '91' appeared that stated Object variable or with block variable not
set. How do I correct my loop statement to run correctly?