Transposing data in columns into a single row?

  • Thread starter Thread starter deostroll
  • Start date Start date
D

deostroll

Hi,

Let us say we have data stored vertically in a column ($A2:$A6). Now
is there a feature by which I can transpose that data into a single
row; meaning get data in a row like A$2:E$2?

--deostroll
 
Copy then Paste Special>Transpose.

Note: the transpose range cannot overlap the original range so select say B2
before transposing.

Delete the original range after you're done.


Gord Dibben MS Excel MVP
 
Let us say we have data stored vertically in a column ($A2:$A6). Now
is there a feature by which I can transpose that data into a single
row; meaning get data in a row like A$2:E$2?
Click to expand...

Below is a macro that you might consider using. Simply select the range of
data from a column and kick off the macro (from, say, a CommandButton click
event) and that data will be moved to consecutive columns. If you don't
specify where, the macro will start placing the "columnized" data at the top
cell of the selected range. If you pass in a range to the optional argument,
the data will be "columnized" starting in the top, left cell of the range
(assuming you pass in a range that is not a single cell). If your data
selection is something other than a single column, you will be warned with a
MessageBox. The "columnized" data can cross the selected data column with no
problem.

Rick

Sub ColumnToRow(Optional NewStartCell As Range)
Dim X As Long
Dim StartRow As Long
Dim StartCol As Long
Dim R As Range
Dim Items() As String
Set R = Selection
If R.EntireColumn.Count > 1 Then
MsgBox "You must select a single column only!", vbExclamation
Else
If NewStartCell Is Nothing Then
StartRow = R.Row
StartCol = R.Column
Else
StartRow = NewStartCell.Row
StartCol = NewStartCell.Column
End If
If R.Count > 1 Then
ReDim Items(0 To R.Count - 1)
For X = 0 To R.Count - 1
Items(X) = Cells(R.Row + X, R.Column).Value
Next
R.Clear
For X = 0 To UBound(Items)
Cells(StartRow, StartCol + X).Value = Items(X)
Next
End If
End If
End Sub
 
Back
Top