I need to loop the code below to find all instances within a spreadsheet.

Can anyone help me with this?

Sub cleanup()
On Error GoTo Err

lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
r1 = Cells.Find(What:="use start", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
'MsgBox r1
r2 = Cells(r1, 1).End(xlDown).Row + 1
'MsgBox r2
Rows(r2).Resize(lr - r2).Delete
r3 = Cells.Find(What:="Agent:", After:=Cells(r1 - 1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Row + 2
'MsgBox r3
Rows(r3).Resize(r1 - r3).Delete
Rows(1).Resize(r3 - 4).Delete
Err:
End Sub

In the below line when lr and r2 becomes same it returns an error.

Rows(r2).Resize(lr - r2).Delete

Instead of placing Msgbox between the code; please debug using function key
F8 to understand where it is going wrong..

If this post helps click Yes
---------------
Jacob Skaria


"Stan" wrote:

I need to loop the code below to find all instances within a spreadsheet.

Can anyone help me with this?

Sub cleanup()
On Error GoTo Err

lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
r1 = Cells.Find(What:="use start", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
'MsgBox r1
r2 = Cells(r1, 1).End(xlDown).Row + 1
'MsgBox r2
Rows(r2).Resize(lr - r2).Delete
r3 = Cells.Find(What:="Agent:", After:=Cells(r1 - 1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Row + 2
'MsgBox r3
Rows(r3).Resize(r1 - r3).Delete
Rows(1).Resize(r3 - 4).Delete
Err:
End Sub

Well I have a spreadsheet with around 10k rows. Within the data I need to
find any rows that have the text "Use Start" and if found, also find rows
immediately above it that have the words "Mgr.", and "Agent" in them. Then I
need to find rows immediately below the found text that include the words
"Sun1", "Mon1". "Tue1",...."Sat1".

Any thoughts on how to best write this?

"Jacob Skaria" wrote:

In the below line when lr and r2 becomes same it returns an error.

Rows(r2).Resize(lr - r2).Delete

Instead of placing Msgbox between the code; please debug using function key
F8 to understand where it is going wrong..

If this post helps click Yes
---------------
Jacob Skaria


"Stan" wrote:

I need to loop the code below to find all instances within a spreadsheet.

Can anyone help me with this?

Sub cleanup()
On Error GoTo Err

lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
r1 = Cells.Find(What:="use start", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
'MsgBox r1
r2 = Cells(r1, 1).End(xlDown).Row + 1
'MsgBox r2
Rows(r2).Resize(lr - r2).Delete
r3 = Cells.Find(What:="Agent:", After:=Cells(r1 - 1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Row + 2
'MsgBox r3
Rows(r3).Resize(r1 - r3).Delete
Rows(1).Resize(r3 - 4).Delete
Err:
End Sub

Where would the text appear? Is it just one single string? Need a bit more
process. If you find Use Start, is it all instances of Mgr and Agent?

Don't forget to put Application.ScreenUpdating = False and
Application.DisplayAlerts = False at the start of the script (and set them
back to True at the end). This will make it run much faster with lots of
data.




"Stan" wrote:

Well I have a spreadsheet with around 10k rows. Within the data I need to
find any rows that have the text "Use Start" and if found, also find rows
immediately above it that have the words "Mgr.", and "Agent" in them. Then I
need to find rows immediately below the found text that include the words
"Sun1", "Mon1". "Tue1",...."Sat1".

Any thoughts on how to best write this?

"Jacob Skaria" wrote:

In the below line when lr and r2 becomes same it returns an error.

Rows(r2).Resize(lr - r2).Delete

Instead of placing Msgbox between the code; please debug using function key
F8 to understand where it is going wrong..

If this post helps click Yes
---------------
Jacob Skaria


"Stan" wrote:

I need to loop the code below to find all instances within a spreadsheet.

Can anyone help me with this?

Sub cleanup()
On Error GoTo Err

lr = Cells(Rows.Count, 1).End(xlUp).Row + 1
'MsgBox lr
r1 = Cells.Find(What:="use start", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
'MsgBox r1
r2 = Cells(r1, 1).End(xlDown).Row + 1
'MsgBox r2
Rows(r2).Resize(lr - r2).Delete
r3 = Cells.Find(What:="Agent:", After:=Cells(r1 - 1, 1), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False).Row + 2
'MsgBox r3
Rows(r3).Resize(r1 - r3).Delete
Rows(1).Resize(r3 - 4).Delete
Err:
End Sub