Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for rows in one sheet and copy into another sheet based on customer id | Excel Worksheet Functions | |||
word search | Excel Discussion (Misc queries) | |||
Search Word | Excel Worksheet Functions | |||
1 word search | New Users to Excel | |||
need formula to search column for a word and return another word | Excel Discussion (Misc queries) |