Convert Single Pair of Columns to 3 Column (Pair) List

I want to convert a list of names and values listed as follows:

NAME AMOUNT
Name1 Value1
Name2 Value2
Name3 Value3
Name4 Value4
Name5 Value5
Name6 Value6
Name7 Value7
Name8 Value8

etc

into 3 pairs of equal length columns with headers



NAME AMOUNT NAME AMOUNT NAME AMOUNT
Name1 Value1 Name43 Value43 Name76 Value76
Name2 Value2 Name44 Value44 Name77 Value77
Name3 Value3 Name45 Value45 Name78 Value78
Name4 Value4 Name46 Value46 Name79 Value79
Name5 Value5 Name47 Value47 Name80 Value80
Name6 Value6 Name48 Value48 Name81 Value81
Name7 Value7 Name49 Value49 Name82 Value82
Name8 Value8 Name50 Value50
etc etc remainder in 3rd column

I am able to determine the number of rows in each column by using the Int and Mod function after counting the number of entries in the original list.

Sub ThreeColumns()

Dim ThreeColums() As Variant
Dim Rows As Long, Columns As Long


Sheet1.Activate ' to ensure we are on the correct worksheet

Rows = Range("A1", Range("A1").End(xlDown)).Cells.Count - 1
Columns = Range("A1", Range("A1").End(xlToRight)).Cells.Count - 1

ReDim ThreeColumns(0 To Rows, 0 To Columns)

Etc.....

End Sub

I can figure out how to find the length of each column using the Int and Mod process on the Rows value but I cannot figure out the the code to read the original pairs of values from the list and then write these values into the 3 column pairs shown above.

I would be grateful if someone would kindly help.

Thank you for your speedy response. It is late here in the UK and so I will give it a go over the weekend and let you know the outcome.

I used the code to produce the result shown in the attached file ThreeColumns1.jpg
I tried to change the code to produce the result that I actually want in attached file ThreeColumns2.jpg
but without much success. Sadly my knowledge of the more advanced VBA code that you used is lacking.

I would be grateful if you would amend the code to:

1. Copy the Name and Subs values from cells starting on Row 8 (for example)
2. Copy and write the Name and Subs values starting at Row 9 and writing them into 3 columns with labels to produce the desired result (Three Columns2.jpg)
3. Change the column widths to set values as shown in ThreeColumns2.

Many thanks for providing the original code and I hope I am not asking too much for the additional modifications to the code.

I am sorry that it has taken some time to respond to this topic - I have been away on holiday. I used your revised code and it worked. Many thanks for your assistance.

I have finally got round to solving the problem with your assistance. I used the
following VBA code to generate 3 equal sized columns from a long list (one column).

Please Login or Register  to view this content.