# Copy columns one below another

Solved/Closed
sun_dands - May 11, 2010 at 11:36 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 13, 2010 at 08:19 AM

hi
i have data in the following form
a b c d
1 6 11 16
2 7 12 17
3 8 13 18
4 9 14 19
5 10 15 20

and i need to move it into
a
1
2
3
4
5
6
7
8

i hve checked the web could not find anything. most of hte solutions were pointing at
1
6
11
16
2
7
12
17
but that is not what i want and i am very desperate to solve this ....can anyone please help.

I should be able to select the range and black data should be treated as O or " "
Related:

## 2 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 12, 2010 at 04:04 PM
Try this. It presumes that you want to copy col 1-2 all the time. At copy of columns down should start at column 4

Sub CopyPasteSpecial()
Dim lMaxRows As Long
Dim iMaxCols As Integer
Dim iCol As Integer
Dim iStartAtCol As Integer
Dim RangeToCopy As Range
Dim lNextAtRow As Long

iMaxCols = Cells(1, Columns.Count).End(xlToLeft).Column
lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row

iStartAtCol = 4

Set RangeToCopy = Range(Cells(2, 1), Cells(lMaxRows, iStartAtCol - 2))
lNextAtRow = lMaxRows + 1
For iCol = iStartAtCol To iMaxCols

RangeToCopy.Copy
Cells(lNextAtRow, "A").Select

ActiveSheet.Paste

Range(Cells(2, iCol), Cells(lMaxRows, iCol)).Copy

Range(Cells(lNextAtRow, iStartAtCol - 1), Cells(lNextAtRow + lMaxRows - 2, iStartAtCol - 1)).PasteSpecial

lNextAtRow = lNextAtRow + lMaxRows - 1
Next iCol

Range(Cells(1, iStartAtCol), Cells(lMaxRows, iMaxCols)).Delete

Set RangeToCopy = Nothing

End Sub
that works bang on....thank you perfect.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 13, 2010 at 08:19 AM
and thank you for a rare feedback that one get here :)

Cheers
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 11, 2010 at 11:44 AM
what you mean by "black data"

Are you looking simply to copy all columns under column A?
i am sorry, that was a typo. it is blank data not black data.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 11, 2010 at 03:21 PM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
https://authentification.site/files/22393996/eg_for_col_transpose.xlsx

i have given a small eg. the actual data would have 25 to 30 columns and more than 5000 rows