Format Columnar Text File

koolwaters

Active Member
Joined
May 16, 2007
Messages
403
Hi,

I have a text file where the pages are arranged vertically. Each page hold 15 records and then the next 15 records appear on the next page. The data essentially consists of four columns and the headings are Customer ID, Customer Name, Customer Type, Sequence No. The number of records vary and I need assistance in automating the import process.

When I use the text to columns wizard, I will get the first 15 records in columns A:D then the next 15 in E:H, I:L, etc.

What I really want is the data in columns E:H under the last record in columns A:D and the data in I:L under that, etc.

Just under the last record, the text 'Total Count' appears and the number of records, which in most instances are more than 1000 so it is tedious to cut and paste.

I hope I have been clear in my explanation.

Thanks.
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I have been able to get through to some extent with the code but I am having an issue looping through the columns as this will be my first time working with loop statements. The below code "works" but only the data from the last 4 columns are cut and pasted. The test file I am working on has data across to columns HT.

Code:
Sub CutPasteColumns()
Dim myLastRow As Long, myLastColumn As Long
Dim i As Integer

myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

i = myLastColumn
    Do Until IsEmpty(Cells(2, myLastColumn))
        Range(Cells(2, myLastColumn - 3), Cells(17, myLastColumn)).Cut Range("A" & myLastRow + 1)
        Application.CutCopyMode = False
        Debug.Print myLastColumn
    Loop
End Sub

This code below does not work and Excel hangs up each time I attempt to run the code.

Code:
Sub CutPasteColumns()
Dim myLastRow As Long, myLastColumn As Long
Dim i As Integer

myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column

i = myLastColumn - 4
    Do Until IsEmpty(Cells(2, i))
        Range(Cells(2, myLastColumn - 3), Cells(17, myLastColumn)).Cut Range("A" & myLastRow + 1)
        Application.CutCopyMode = False
        Debug.Print myLastColumn
    Loop
End Sub

I figured it would be easier to start with the last column and move to A.

Essentially, what I am trying to do is cut the data from HQ2:HT16 and paste it in the next blank cell in column A, then cut the data in HM2:HP16 and paste it in the next blank cell in column A, etc until E2:H16 as A2:D16 will contain the first 15 records in the data.

All I need is a way to loop from HT to E.

Thanks.
 
Upvote 0
How about
Code:
Sub CutPasteColumns()
   Dim myLastColumn As Long
   Dim i As Integer
   
   myLastColumn = Cells(2, Columns.Count).End(xlToLeft).Column
   
   For i = 5 To myLastColumn Step 4
      Range(Cells(2, i), Cells(17, i + 3)).Cut Range("A" & Rows.Count).End(xlUp).Offset(1)
   Next i
   Application.CutCopyMode = False
End Sub
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top