Improve existing VBA loop to clean SAP output for MS Access

[audan2009]

So, I get an SAP output from a SPOOL. It comes out as a terrible "|" delimitated .CSV file in multiple "pages" which are actually blank rows with "---------" in between and some other text. I have to clean it before importing it in to Access which I have a VBA script for.

I have this code to do text-to-columns and it works great!

[FONT=arial]Sub TextToColumns()[/FONT]
[FONT=arial]    Dim ws As Worksheet[/FONT]

[FONT=arial]    For Each ws In Worksheets[/FONT]
[FONT=arial]        ws.Columns("A:A").<wbr>TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _[/FONT]
[FONT=arial]        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _[/FONT]
[FONT=arial]        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"[/FONT]
[FONT=arial]    Next ws[/FONT]
[FONT=arial]End Sub[/FONT]

These are the steps I take to clean from the raw output from SAP:

1) Concatenate all cells in the 1st row like "=A1&B1&C1.....&Z1" ..... because SAP will continue delimitation overflow in to column B,C and some others... its pretty variable depending on the amount of columns I'm exporting from excel.

I do that in column AA then fill it down till there are no more rows. Sometimes is 3,000 rows and sometimes its 500,000 rows.

2) Once that is all in AA, I copy it to a new sheet in A1

3) Text-to-columns (now I can use that VBA code)

4) TRIM all column headings (I usually copy paste in a new sheet, transpose, trim, copy/paste values, transpose back)

5) Filter and delete all things in column "B" that either BLANK or begin with "----"


If someone could help me, I'd freak out. Kind of looking like this at first then

 

[audan2009]

This looks like its working to combine the column headings. Now I need it to get it to copy to another spreadsheet then perform the text to columns.

Sub ConCat()    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .Offset(, 10).Value = Evaluate(.Address & "& """" & " & .Offset(, 2).Address)
    End With
End Sub