Copy columns one below another [Solved/Closed]

sun_dands - May 11, 2010 at 11:36 AM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- 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
See more 

7 replies

Best answer
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 12, 2010 at 04:04 PM
1
Thank you
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

Thank you, rizvisa1 1

Something to say? Add comment

CCM has helped 1703 users this month

that works bang on....thank you perfect.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 13, 2010 at 08:19 AM
and thank you for a rare feedback that one get here :)

Cheers
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 11, 2010 at 11:44 AM
0
Thank you
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 11, 2010 at 03:21 PM
Could you please upload a sample file with sample data etc on some shared site like http://www.speedyshare.com/ and post back here the link to allow better understanding of how it is now and how you foresee.
http://www.speedyshare.com/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