Transpose Problem
 

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.

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

---
HTH
Jason
Atlanta, GA

-----Original Message-----
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.
.

Biman wrote:
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

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

Thanks Jason. It works perfectly.

-Biman

"Jason Morin" wrote:

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

---
HTH
Jason
Atlanta, GA

-----Original Message-----
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.
.

Thanks Alan.

"Alan Beban" wrote:

Biman wrote:
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