copy columns one below another Solved/Closed

Question

Configuration: Windows XP / Internet Explorer 7.0
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

rizvisa1 · Answer

what you mean by "black data"

Are you looking simply to copy all columns under column A?

rizvisa1 · Answer

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

Copy columns one below another

2 responses

Similar discussions

Subscribe To Our Newsletter!