pastespecial

[alaomair]

can anyone help me with creating a udf for paste special transpose

 

[Dave Peterson]

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.

 

[ShaneDevenshire]

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.

 

[alaomair]

i tried this but no value
Function transpose(a As Range)
a.Copy

ActiveCell.PasteSpecial transpose:=True
End Function

 

[Dave Peterson]

How are you calling that function?

If it's in a formula on a cell on a worksheet, it won't work.

 

[alaomair]

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

 

[Dave Peterson]

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

 

[alaomair]

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)