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