Removing group of repetitive rows from large excel file.

I have to prepare some files for our friendly neighborhood auditor. These files are generated by our month end procedure in Accpac Plus for DOS.

Two files are generated: an AP (purchases and expenses) file and a OE (sales) file.

Basically what I need to do is take these 24 total text files and open them with Excel and set them up so totals for GL accounts can be viewed easily etc etc.

My problem is that Accpac generates these files with page headers. Each file must have at least 100 pages so when I open them in excel I get the date, page number and column headers every 60 rows or so.

It wouldn't be so bad if these page headers occupied a single line but they span about 8 or 9 lines.

What's the quickest and least painful way I can remove these lines either in Notepad, Word or Excel?

Thanks.
LVL 3
MescronomiconAsked:
Who is Participating?
 
R_RajeshCommented:
Code assumes that the header always starts with "Date:" and spans eight rows...

Sub Test()
Dim c As Range, rng As Range
With Cells
    Set c = .Find("Date:", LookIn:=xlFormulas)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            If rng Is Nothing Then Set rng = Rows(c.Row & ":" & c.Row + 7)
            Set rng = Union(rng, Rows(c.Row & ":" & c.Row + 7))
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
End With
rng.Delete
End Sub
0
 
gtglonerCommented:
Could you post an example of what you see when you have your file in Excel?
0
 
MescronomiconAuthor Commented:
Date: Dec 02 2004    3:22pm                           XXXXXXXXXXXXXXXXXXX                                             Page:   1
G/L Transactions

Report information in detail.
Sort G/L transactions by account.

                          Entry                                                                      
 Acct.  Dept. Date          No. Src.Code Reference    Description                              Debit          Credit

10x000        Nov 01 2004     8 AP - MC  CONSEILLER I     3-   3-    1        xxxxx                       xx,xxx.xx
              Nov 01 2004     9 AP - MC  ZENTIL-BRATT     3-   3-    2        xxxxxx                      xx,xxx.xx
              Nov 01 2004    10 AP - MC  GWL REALTY A     3-   3-    3        xxxxxx                        xx,xxx.xx
              Nov 01 2004    11 AP - MC  GROSVENOR CA     3-   3-    4        xxxxx                      xx,xxx.xx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
MescronomiconAuthor Commented:
It's aligns however in the actual document.
0
 
MescronomiconAuthor Commented:
This is the stuff that repeats every 60 rows or so:


Date: Dec 02 2004    3:22pm                           XXXXXXXXXXXXXXXXXXX                                             Page:   1
G/L Transactions

Report information in detail.
Sort G/L transactions by account.

                          Entry                                                                      
 Acct.  Dept. Date          No. Src.Code Reference    Description                              Debit          Credit


0
 
MescronomiconAuthor Commented:
Awesome! Worked like a charm, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.