Transpose Problem

[Guest]

Hi,

I have multiple rows of data spanning across 10 columns. This has to be
transposed onto ONE column where each row of data has to be APPENED one below
the other.
For eg: if the data is available from C1 to L4, this data has to be
transposed onto the column B from B1 to B40.

Is there any simpler way than doing it manually one by one. Please help

-Biman.

 

[Jason Morin]

Try this macro

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to tranpose rows '
'into 1 continuous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''

'Constructive criticism from knowledgable
'VBA programmers welcome - esp. BP!

Dim OrigDataLastRow As Long
Dim AllDataLastRow As Long
Dim RowNdx As Long
Dim ws As Worksheet
Dim CopyRow As Range

Set ws = ActiveWorkbook.ActiveSheet
OrigDataLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
AllDataLastRow = 1

Sheets.Add.Name = "Alldata"

With Application
.ScreenUpdating = False

For RowNdx = 1 To OrigDataLastRow
Set CopyRow = ws.Range(RowNdx & ":" & RowNdx)
CopyRow.Copy
Sheets("Alldata").Cells(AllDataLastRow, 1). _
PasteSpecial Transpose:=True
AllDataLastRow = Sheets("Alldata"). _
Cells(Rows.Count, 1).End(xlUp).Row + 1
Next

.ScreenUpdating = True
.CutCopyMode = False

End With

End Sub

 

[Alan Beban]

Hi,

I have multiple rows of data spanning across 10 columns. This has to be
transposed onto ONE column where each row of data has to be APPENED one below
the other.
For eg: if the data is available from C1 to L4, this data has to be
transposed onto the column B from B1 to B40.

Is there any simpler way than doing it manually one by one. Please help

-Biman.

What goes in B2, the value from C2 or D1? If the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook then

=ArrayReshape(C1:L4,40,1) if the answer above is D1;
=ArrayReshape(C1:L4,40,1,FALSE) if the answer above is C2.

Alan Beban

 

[Herbert Seidenberg]

Here is another way without VBA
1. Edit | Office Clipboard | Clear All
2. Select C1:C4 | Copy
3. Repeat step 2 for each additional column
4. Select B1
5. Office Clipboard | Paste All

 

[Guest]

Thanks Jason. It works perfectly.

-Biman

Try this macro

Sub OneColumn()

''''''''''''''''''''''''''''''''''''''''''
'Macro to tranpose rows '
'into 1 continuous column in a new sheet '
''''''''''''''''''''''''''''''''''''''''''

'Constructive criticism from knowledgable
'VBA programmers welcome - esp. BP!

Dim OrigDataLastRow As Long
Dim AllDataLastRow As Long
Dim RowNdx As Long
Dim ws As Worksheet
Dim CopyRow As Range

Set ws = ActiveWorkbook.ActiveSheet
OrigDataLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
AllDataLastRow = 1

Sheets.Add.Name = "Alldata"

With Application
.ScreenUpdating = False

For RowNdx = 1 To OrigDataLastRow
Set CopyRow = ws.Range(RowNdx & ":" & RowNdx)
CopyRow.Copy
Sheets("Alldata").Cells(AllDataLastRow, 1). _
PasteSpecial Transpose:=True
AllDataLastRow = Sheets("Alldata"). _
Cells(Rows.Count, 1).End(xlUp).Row + 1
Next

.ScreenUpdating = True
.CutCopyMode = False

End With

End Sub

 

[Guest]

Thanks Alan.

What goes in B2, the value from C2 or D1? If the functions in the freely
downloadable file at http://home.pacbell.net/beban are available to your
workbook then

=ArrayReshape(C1:L4,40,1) if the answer above is D1;
=ArrayReshape(C1:L4,40,1,FALSE) if the answer above is C2.

Alan Beban