Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do until loop with use of another macro in loop | Excel Programming | |||
Loop Macro | Excel Programming | |||
VBA Macro Loop | Excel Programming | |||
Using for loop in a macro | Excel Programming | |||
I Need Help with my loop macro | Excel Programming |