Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

macro to auto transpose

  Asked By: Clifton    Date: Feb 07    Category: MS Office    Views: 7412
  

If you wish to transpose a matrix of data you can easily use the
Copy and Paste Special with Transpose selected for the purpose.
However should you require to transpose a single column of multiple
rows of data into a single row of multiple columns of data or vice
versa you may wish to use the two macros that I would wish to
explain below.

Create the two macros as shown below.
Transpose a single column of multiple rows of data into a single row
of multiple columns
(1) To transpose a single column of multiple rows of data just
position cell cursor at the first row of the selected column then
press Ctrl+Shift+R. If you subsequently press Ctrl+Shift+R again at
any other cell cursor position then you are actually choosing
another column of data.
(2) Position cell cursor at the beginning cell where you wish to
place your transposed data and press Ctrl+Shift+C. Your transposed
data will be inserted.
Transpose a single row of multiple columns of data into a single
column of multiple rows
(1) To transpose a single row of multiple columns of data just
position cell cursor at the first column of the selected row then
press Ctrl+Shift+C. If you subsequently press Ctrl+Shift+C again at
any other cell cursor position then you are actually choosing
another row of data.
(2) Position cell cursor at the beginning cell where you wish to
place your transposed data and press Ctrl+Shift+R. Your transposed
data will be inserted.

Sub Rows()
'
' Rows Macro - Assign shortcut key Ctrl+Shift+R
'
On Error GoTo Continue
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
GoTo Laststep
Continue:
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Laststep:
End Sub

Sub Cols()
'
' Cols Macro - Assign shortcut key Ctrl+Shift+C
'
On Error GoTo Continue
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
GoTo Laststep
Continue:
ActiveCell.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Laststep:
End Sub

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Shelia Wells     Answered On: Feb 07

I am trying to run these Macro, but I couldn't.

As you told, I copied the two macros  as Row() and
Col()
I typed some number in a column  with mutilple rows
like 1, 2, 3, 4,5
Then I run Row() Macro, it select all the values.
But when I do CNTL+SHIFT+R, I didn't see any action on
this means after CNTL+SHIFHT+R in the first row  of the
selected column, I place the cursor  in cell  to paste
by CNTL+SHIFT+C. But it didn't insert the value on it.

Can you please let me know how to run this Macro.

 
Didn't find what you were looking for? Find more on macro to auto transpose Or get search suggestion and latest updates.




Tagged: