pastespecial

  • Thread starter Thread starter alaomair
  • Start date Start date
You can't have a UDF that does paste special|transpose.

You could use the builtin excel function =transpose(), but this leaves a
formula.

You could have a Subroutine that does the edit|paste special|transpose. If you
want that, record a macro after you've done the copy and selected the cell to
paste special.
 
Hi,

You now know you can use =TRANSPOSE(Range)

Suppose your range is 5 rows by 10 columns
select a range 10 rows by 5 columns and type, but do NOT enter the formula
Press Shift+Ctrl+Enter

The code to do the transpose command would be:

Sub Transpose()
myRange = InputBox("Enter the address of the range to transpose",
"Transpose")
Range(myRange).Copy
myTarget = InputBox("Enter the destination cell address.", "Transpose")
Range(myTarget).PasteSpecial Transpose:=True
End Sub

This is very simplistic, it would be better done with a user form. But you
might as well do it manually because its faster.


If this helps, please click the Yes button.
 
i tried this but no value
Function transpose(a As Range)
a.Copy

ActiveCell.PasteSpecial transpose:=True
End Function
 
How are you calling that function?

If it's in a formula on a cell on a worksheet, it won't work.
 
will i'm thinking of doing it the old fashioned way by using the
offset
by counting the rows and columns in the range selection
any help there
 
Why not use the builtin function =transpose()?

will i'm thinking of doing it the old fashioned way by using the
offset
by counting the rows and columns in the range selection
any help there
Click to expand...
 
i need it to be automated without the array thing {}
i made this formula that will transpose a row to col
INDIRECT("R"&ROW($A$1)&"c"&COLUMN(INDIRECT(ADDRESS(ROW($A
$1),COLUMN(A1)+ROW(A1)-1,4))),FALSE)
 
Back
Top