Copy columns one below another [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-

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 " "
thanks in advance

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

that works bang on....thank you perfect.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
and thank you for a rare feedback that one get here :)

Cheers
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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