If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
del certain rows based on word search
Hello,
I import about 50 pages of data from another program that leaves page headers which consist of 10 rows of junk. In between these rows of headers, there is aprox. 10 to 12 rows of good data, not always the same number of rows. I would like to end up with just the data and delete the junk rows. The one common identifier (for search purposes) I see is the company name in column E of the top of the header (the 1st row of junk). How would I go about automatically searching out and deleting the row that includes the company name, as well as the 9 tows beneath it?. This header exists about 50 times, so I need something that can search through and delete all of the headers. thanks in advance for any help, Robert |
#2
|
|||
|
|||
del certain rows based on word search
Sub DeleteRows()
FindString = "company name" 'adjust to company name Set b = Range("E:E").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.Resize(10).EntireRow.delete Set b = Range("E:E").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 13:30:02 -0700, Robert wrote: Hello, I import about 50 pages of data from another program that leaves page headers which consist of 10 rows of junk. In between these rows of headers, there is aprox. 10 to 12 rows of good data, not always the same number of rows. I would like to end up with just the data and delete the junk rows. The one common identifier (for search purposes) I see is the company name in column E of the top of the header (the 1st row of junk). How would I go about automatically searching out and deleting the row that includes the company name, as well as the 9 tows beneath it?. This header exists about 50 times, so I need something that can search through and delete all of the headers. thanks in advance for any help, Robert |
#3
|
|||
|
|||
del certain rows based on word search
Sweet! This works great. Thanks Gord!
Robert "Gord Dibben" wrote: Sub DeleteRows() FindString = "company name" 'adjust to company name Set b = Range("E:E").Find(what:=FindString, lookat:=xlWhole) While Not (b Is Nothing) b.Resize(10).EntireRow.delete Set b = Range("E:E").Find(what:=FindString, lookat:=xlWhole) Wend End Sub Gord Dibben MS Excel MVP On Tue, 16 Jun 2009 13:30:02 -0700, Robert wrote: Hello, I import about 50 pages of data from another program that leaves page headers which consist of 10 rows of junk. In between these rows of headers, there is aprox. 10 to 12 rows of good data, not always the same number of rows. I would like to end up with just the data and delete the junk rows. The one common identifier (for search purposes) I see is the company name in column E of the top of the header (the 1st row of junk). How would I go about automatically searching out and deleting the row that includes the company name, as well as the 9 tows beneath it?. This header exists about 50 times, so I need something that can search through and delete all of the headers. thanks in advance for any help, Robert |
Thread Tools | |
Display Modes | |
|
|