Menu
Ask a question Report

How to move data in rows to columns [Solved/Closed]

- - Latest reply:  Ema - Feb 4, 2012 at 06:52 AM
Excel Ask a question Report
The data now is in the below format

1 2 3
9 15 16 20 25
1

This has to come in this format

1
2
3
9
15
16
20
25
1



See more 

1 reply

Best answer
Report
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
 753
1
Thank you
Assumptions
1. Data starts from Row 1 and is to be always copied to column A
2. Rows can be inserted without distortion to data 
Sub TransposeSpecial()
Dim lMaxRows As Long 'max rows in the sheet
Dim lThisRow As Long 'row being processed
Dim iMaxCol As Integer 'max used column in the row being processed

    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    
    lThisRow = 1 'start from row 1
    
    Do While lThisRow < lMaxRows
        
        iMaxCol = Cells(lThisRow, Columns.Count).End(xlToLeft).Column
        
        If (iMaxCol > 1) Then
            Rows(lThisRow + 1 & ":" & lThisRow + iMaxCol - 1).Insert
            Range(Cells(lThisRow, 2), Cells(lThisRow, iMaxCol)).Copy
            Range("A" & lThisRow + 1).Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
            Range(Cells(lThisRow, 2), Cells(lThisRow, iMaxCol)).Clear
            lThisRow = lThisRow + iMaxCol - 1
            lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
        End If
        
        lThisRow = lThisRow + 1
    Loop
End Sub

Say "Thank you" 1

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

CCM 6091 users have said thank you to us this month

Report
-
Thank you very much! Your program helped me a lot.
Ask a question