Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Removing multiple headers from imported text file

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Removing multiple headers from imported text file

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,570
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing multiple headers from imported text file

    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.

  3. #3
    New Member
    Join Date
    Jan 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing multiple headers from imported text file

    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
    3 65475 Mason Material / Labor 632 12/31/2013 999 999 0.01 0.01 zzzzz L Masonry
    4 65525 Deliver/Install Siding 650 __/__/____ 999 999 0.01 0.01 zzzzz C Construction
    5 Company Name
    6 22-Jan 2013 Page: 2
    7 8:39 19 am ACTIVITY LEVEL pr7
    8 New Price <---- Project ed Cost ----> <--- Cost Per Sq Foot --->
    9 Activi ty Grp Expiration Current Future Current Future Selec ted Vendor Name
    10 ====== ======================== ===== ========== ============== ============== ============== =============== ===== =========================
    11 Projec t: 1prj2 Project Name Process ed: 01/21/2013 User: pr7 P roject For Cost : Cost Date: 01/21/2013
    12 Versio n: 088 Project All Plans , Elevatio ns and Options
    13 Plan: mosn2220d Plan Ele vatio n D
    14 Option :
    15 65550 Sand Drywall 625 __/__/____ 999.99 999.99 0.01 0.01 zzzzz Drywall Inc
    16 65575 Deliver Rock 2 840 __/__/____ 999.99 999.99 0.01 0.01 zzzzz J Bros
    17 65700 Blown Insulation 620 12/31/2012 999.99 999.99 0.01 0.01 zzzzz A Insulation
    18
    19
    20
    21
    22
    23 ------------ ------------ ------------ ------------
    24 Plan/O ption Total --> 999.99 999.99 0.01 0.01
    25 ============ ============ ============ ============
    26 Company Name
    27 22-Jan 2013 Page: 2
    28 8:39 19 am ACTIVITY LEVEL pr7
    29 New Price <---- Project ed Cost ----> <--- Cost Per Sq Foot --->
    30 Activi ty Grp Expiration Current Future Current Future Selec ted Vendor Name
    31 ====== ======================== ===== ========== ============== ============== ============== =============== ===== =========================
    32 Projec t: 1prj2 Project Name Process ed: 01/21/2013 User: pr7 P roject For Cost : Cost Date: 01/21/2013
    33 Versio n: 088 Project All Plans , Elevatio ns and Options
    34 Plan: mosn2220d Plan Ele vatio n D
    35 Option :
    36 65550 Sand Drywall 625 __/__/____ 999.99 999.99 0.01 0.01 zzzzz Drywall Inc
    37 65575 Deliver Rock 2 840 __/__/____ 999.99 999.99 0.01 0.01 zzzzz J Bros
    38 65700 Blown Insulation 620 12/31/2012 999.99 999.99 0.01 0.01 zzzzz A Insulation
    Sheet1

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,570
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing multiple headers from imported text file

    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.

  5. #5
    New Member
    Join Date
    Jan 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing multiple headers from imported text file

    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.

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    9,570
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Removing multiple headers from imported text file

    Quote Originally Posted by dcbuzzell View Post
    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com