find, delete, repeat loop
 

Hi,
I am trying to remove a header that shows up on the top of every page
of a text file I imported. I am working with a file with about 2000
headers and have one with 2500 headers and 5000 headers waiting for me
to finish this code.

It has been many years since I have written code.

I have recorded a macro that finds the header by finding the first word
in it and then selected a range 10 cells deep by 2 wide. It then
removes the rows. The macro works fine when I run it but I am having
trouble getting it to loop.

I keep getting and EXPECTED END SUB error when I try to run this. I
added some looping code I found in the help area. I don't know if this
is the best way to do this but I would appreciate any help. I would
really really appreciate it. Thanks, Kim

Sub headergone()
'
' headergone Macro
' This macro removes the header from the top of each page
'
' Keyboard Shortcut: Ctrl+h
'
Sub ChkFirstUntil()
counter = 0
myNum = 20
Do Until myNum = 10
myNum = myNum - 1
counter = counter + 1



Cells.Find(What:="STAT", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate
ActiveCell.Range("A1:B10").Select
Selection.EntireRow.Delete

Loop
MsgBox "The loop made " & counter & " repetitions."
End Sub
End Sub

find, delete, repeat loop
 

Sub headergone()
'
' headergone Macro
' This macro removes the header from the top of each page
'
' Keyboard Shortcut: Ctrl+h
'
Dim counter as Long
Dim rng as Range
counter = 0
Do
counter = counter + 1


set rng = Nothing
set rng = Cells.Find(What:="STAT", _
After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False)
if not rng is Nothing then
rng.Range("A1:B10").EntireRow.Delete
else
exit do
end if
Loop
MsgBox "The loop made " & counter & " repetitions."

End Sub

--
Regards,
Tom Ogilvy

" wrote:

Hi,
I am trying to remove a header that shows up on the top of every page
of a text file I imported. I am working with a file with about 2000
headers and have one with 2500 headers and 5000 headers waiting for me
to finish this code.

It has been many years since I have written code.

I have recorded a macro that finds the header by finding the first word
in it and then selected a range 10 cells deep by 2 wide. It then
removes the rows. The macro works fine when I run it but I am having
trouble getting it to loop.

I keep getting and EXPECTED END SUB error when I try to run this. I
added some looping code I found in the help area. I don't know if this
is the best way to do this but I would appreciate any help. I would
really really appreciate it. Thanks, Kim

Sub headergone()
'
' headergone Macro
' This macro removes the header from the top of each page
'
' Keyboard Shortcut: Ctrl+h
'
Sub ChkFirstUntil()
counter = 0
myNum = 20
Do Until myNum = 10
myNum = myNum - 1
counter = counter + 1



Cells.Find(What:="STAT", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate
ActiveCell.Range("A1:B10").Select
Selection.EntireRow.Delete

Loop
MsgBox "The loop made " & counter & " repetitions."
End Sub
End Sub

find, delete, repeat loop
 

You have a subroutine nested inside a subroutine. That's an invalid
structure. Maybe try removing "Sub ChkFirstUntil()" and one of the "End Sub"
statements.

" wrote:

Hi,
I am trying to remove a header that shows up on the top of every page
of a text file I imported. I am working with a file with about 2000
headers and have one with 2500 headers and 5000 headers waiting for me
to finish this code.

It has been many years since I have written code.

I have recorded a macro that finds the header by finding the first word
in it and then selected a range 10 cells deep by 2 wide. It then
removes the rows. The macro works fine when I run it but I am having
trouble getting it to loop.

I keep getting and EXPECTED END SUB error when I try to run this. I
added some looping code I found in the help area. I don't know if this
is the best way to do this but I would appreciate any help. I would
really really appreciate it. Thanks, Kim

Sub headergone()
'
' headergone Macro
' This macro removes the header from the top of each page
'
' Keyboard Shortcut: Ctrl+h
'
Sub ChkFirstUntil()
counter = 0
myNum = 20
Do Until myNum = 10
myNum = myNum - 1
counter = counter + 1



Cells.Find(What:="STAT", After:=ActiveCell, LookIn:=xlFormulas,
LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
True, SearchFormat:=False).Activate
ActiveCell.Range("A1:B10").Select
Selection.EntireRow.Delete

Loop
MsgBox "The loop made " & counter & " repetitions."
End Sub
End Sub