Removing multiple headers from imported text file

dcbuzzell

New Member
Joined
Jan 22, 2013
Messages
29
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,793
Office Version
2013
Platform
Windows
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

New Member
Joined
Jan 22, 2013
Messages
29
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
ABCDEFGHIJKL
365475Mason Material / Labor63212/31/20139999990.010.01zzzzzL Masonry
465525Deliver/Install Siding650__/__/____9999990.010.01zzzzzC Construction
5Company Name
622-Jan2013Page: 2
78:3919 amACTIVITY LEVELpr7
8New Price<---- Projected Cost ----><--- Cost PerSq Foot --->
9ActivityGrpExpirationCurrentFutureCurrentFutureSelected Vendor Name
10====================================================================================================================================
11Project: 1prj2 Project NameProcessed: 01/21/2013User: pr7 Project For Cost:Cost Date: 01/21/2013
12Version: 088 Project AllPlans, Elevations and Options
13Plan:mosn2220d Plan Elevation D
14Option:
1565550Sand Drywall625__/__/____999.99999.990.010.01zzzzzDrywall Inc
1665575Deliver Rock 2840__/__/____999.99999.990.010.01zzzzzJ Bros
1765700Blown Insulation62012/31/2012999.99999.990.010.01zzzzzA Insulation
18
19
20
21
22
23------------------------------------------------
24Plan/OptionTotal -->999.99999.990.010.01
25================================================
26Company Name
2722-Jan2013Page: 2
288:3919 amACTIVITY LEVELpr7
29New Price<---- Projected Cost ----><--- Cost PerSq Foot --->
30ActivityGrpExpirationCurrentFutureCurrentFutureSelected Vendor Name
31====================================================================================================================================
32Project: 1prj2 Project NameProcessed: 01/21/2013User: pr7 Project For Cost:Cost Date: 01/21/2013
33Version: 088 Project AllPlans, Elevations and Options
34Plan:mosn2220d Plan Elevation D
35Option:
3665550Sand Drywall625__/__/____999.99999.990.010.01zzzzzDrywall Inc
3765575Deliver Rock 2840__/__/____999.99999.990.010.01zzzzzJ Bros
3865700Blown Insulation62012/31/2012999.99999.990.010.01zzzzzA Insulation

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,793
Office Version
2013
Platform
Windows
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

New Member
Joined
Jan 22, 2013
Messages
29
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,793
Office Version
2013
Platform
Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,096,187
Messages
5,448,862
Members
405,535
Latest member
KLFT

This Week's Hot Topics

Top