Removing multiple headers from imported text file

[dcbuzzell]

I am working in Excel 2010 with an imported text file drawn from an older program where headers from a print version are repeated for each page - roughly 3000 headers in one of 15 reports I need to aggregate.

I have to keep the headers initiially in order to add data from the headers to the data I want to keep. Following that, I'd like a quick way to remover all those headers.

At the top of each header is our company name, which will not wind up in a consistent column, due to differences in the text-to-columns conversion across different reports. However, the company name will only occur as the entire content of a cell in the header - in other words, searching for that name will result only in landing on the top row of a header. Also, the header height might vary across reports, but on the initial report, it is 10 rows in height on this particular report. I want to delete the entire row for all 10 rows of the header.

My thought was to find the company name, select that row and user input for the # of rows below it, delete the header and then cycle to the next header and repeat.

One other trick - part way into the report, there are three summary lines added to the end of a page that I would like to delete. So on those headers, I need to find the company name, then select a range that includes the three rows above it and the entirety of the header below. I'm open to suggestions here - best I can think of is, using the idea above, executing a first pass deleting the 10 rows, then running it through again looking for another piece of unique info in the three remaining rows (which it has - a series of cells with "------------" on one row) and with the user input changed to 3 for rows, it could be done.

Thanks in advance for any help.

 

[JLGWhiz]

Hi dcbuzzell, Welcome to MrExcel forum
Are the headers you are referring to colomn headers or are they page headers that are created through the Page Setup feature? Also, if you could put up a screen shot of your worksheet, it would help. See the posting guidelines for tools to post screen shots, etc.

 

[dcbuzzell]

Thank you for your response. The headers are page headers created in the text file by the program where the report was run/"printed" - nothing created thru Page Setup or Excel.

Below, I have consolidated the two headers together in one range - the second shows the 3 additional rows that appear at the end of pages (and thus, top of the header) part-way thru the report.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L
Mason Material / Labor	zzzzz	L Masonry
Deliver/Install Siding	__/__/____	zzzzz	C Construction
Company Name
Page: 2
19 am	ACTIVITY LEVEL	pr7
New Price	<---- Project	ed Cost ---->	<--- Cost Per	Sq Foot --->
Activi	ty	Grp	Expiration	Current	Future	Current	Future	Selec	ted Vendor Name
======	========================	=====	==========	==============	==============	==============	===============	=====	=========================
Projec	t: 1prj2 Project Name	Process	ed: 01/21/2013	User: pr7 P	roject For Cost	:	Cost Date: 01/21/2013
Versio	n: 088 Project All	Plans	, Elevatio	ns and Options
Plan:	mosn2220d Plan Ele	vatio	n D
Option	:
Sand Drywall	__/__/____	zzzzz	Drywall Inc
Deliver Rock 2	__/__/____	zzzzz	J Bros
Blown Insulation	zzzzz	A Insulation
------------	------------	------------	------------
Plan/O	ption	Total -->
============	============	============	============
Company Name
Page: 2
19 am	ACTIVITY LEVEL	pr7
New Price	<---- Project	ed Cost ---->	<--- Cost Per	Sq Foot --->
Activi	ty	Grp	Expiration	Current	Future	Current	Future	Selec	ted Vendor Name
======	========================	=====	==========	==============	==============	==============	===============	=====	=========================
Projec	t: 1prj2 Project Name	Process	ed: 01/21/2013	User: pr7 P	roject For Cost	:	Cost Date: 01/21/2013
Versio	n: 088 Project All	Plans	, Elevatio	ns and Options
Plan:	mosn2220d Plan Ele	vatio	n D
Option	:
Sand Drywall	__/__/____	zzzzz	Drywall Inc
Deliver Rock 2	__/__/____	zzzzz	J Bros
Blown Insulation	zzzzz	A Insulation

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]
[TD="align: right"]65475[/TD]

[TD="align: right"]632[/TD]
[TD="align: right"]12/31/2013[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]65525[/TD]

[TD="align: right"]650[/TD]

[TD="align: right"]999[/TD]
[TD="align: right"]999[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]22-Jan[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]8:39[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]65550[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]65575[/TD]

[TD="align: right"]840[/TD]

[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]65700[/TD]

[TD="align: right"]620[/TD]
[TD="align: right"]12/31/2012[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]

[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"]22-Jan[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]28[/TD]
[TD="align: right"]8:39[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]30[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]31[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]32[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]33[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]34[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]35[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]36[/TD]
[TD="align: right"]65550[/TD]

[TD="align: right"]625[/TD]

[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]37[/TD]
[TD="align: right"]65575[/TD]

[TD="align: right"]840[/TD]

[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]38[/TD]
[TD="align: right"]65700[/TD]

[TD="align: right"]620[/TD]
[TD="align: right"]12/31/2012[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]999.99[/TD]
[TD="align: right"]0.01[/TD]
[TD="align: right"]0.01[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[JLGWhiz]

So the header runs from the row where it says "Company Name" to the row where it says "Option". Is that correct? If so, this bit of code would take out those rows.
Try it on a copy before applying to the original.

Code:
Sub delHead()
Dim sh As Worksheet, lr As Long, rng As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells.Find(What:="*", After:=sh.Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
Set rng = sh.Range("A1:A" & lr)
For i = lr To 1 Step -1
If WorksheetFunction.CountIf(sh.Cells(i, 1).Resize(1, 50), "Company Name") > 0 Then
sh.Cells(i, 1).Resize(10, 1).EntireRow.Delete
End If
Next
End Sub
Code:

Unfortunately, I cannot come up with anything on the trailing data. It seems to be too erratic to try and do by code.

 

[dcbuzzell]

You are correct, those are the rows I need to remove. Works perfect - thanks for the solution. I was able to adapt that to remove the trailing data as well by repeating the code, changing the searched data to "-----" and the number of rows to 13. I run that first, then run the above and all the bad stuff is gone.

Thanks again so much for the quick and concise response.

 

[JLGWhiz]

You are correct, those are the rows I need to remove. Works perfect - thanks for the solution. I was able to adapt that to remove the trailing data as well by repeating the code, changing the searched data to "-----" and the number of rows to 13. I run that first, then run the above and all the bad stuff is gone.

Thanks again so much for the quick and concise response.

Happy it worked for you, Thanks for the feedback.

Regards, JLG