Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why can the number of iterations repeat many times as a loop?? | Excel Discussion (Misc queries) | |||
Repeat keystrokes to delete character in column | Excel Worksheet Functions | |||
need macro script - repeat to delete 2 rows | Excel Discussion (Misc queries) | |||
Loops to find blanks then loop to find populated | Excel Programming | |||
Excel VBA - Repeat for loop until blank rows | Excel Programming |