Treansposition - use arrays?

[Risky Dave]

Hi,

I have a set of data (13 cells) stored adjacently in a known column on a
sheet. What I want to do is transpose this to a row on a different sheet.
From simliar entries on this newsgroup I think I'm going to have to set up
some arrays to manage this, but not being a programmer by profession (I'm
just learning this stuff as I go along!) I'm getting a bit confused.

What I need to do is transpose

A B C
1 ADatum1 BDatum1 CDatum1
2 ADatum2 BDatum2 CDatum2
3 ADatum3 BDatum3 CDatum3

into:
A B C
1 ADatum1 ADatum2 ADatum3
2 BDatum1 BDatum2 BDatum3
3 CDatum1 CDatum2 CDatum3

There are 13 separate data entries per source column, but an unknown number
of columns with data in them. Columns with data are always adjacent to each
other, so I can use the presence of a blank cell to indicate when I have
reached the end of either a data set in a column or the last column of the
whole set.

I'd be much appreciative if anyone can show me the syntax for getting this
to work.

This is in Office '07 under Vista if that makes a difference.

TIA

Dave

 

[Lars-Åke Aspelin]

Hi,

I have a set of data (13 cells) stored adjacently in a known column on a
sheet. What I want to do is transpose this to a row on a different sheet.
From simliar entries on this newsgroup I think I'm going to have to set up
some arrays to manage this, but not being a programmer by profession (I'm
just learning this stuff as I go along!) I'm getting a bit confused.

What I need to do is transpose

A B C
1 ADatum1 BDatum1 CDatum1
2 ADatum2 BDatum2 CDatum2
3 ADatum3 BDatum3 CDatum3

into:
A B C
1 ADatum1 ADatum2 ADatum3
2 BDatum1 BDatum2 BDatum3
3 CDatum1 CDatum2 CDatum3

There are 13 separate data entries per source column, but an unknown number
of columns with data in them. Columns with data are always adjacent to each
other, so I can use the presence of a blank cell to indicate when I have
reached the end of either a data set in a column or the last column of the
whole set.

I'd be much appreciative if anyone can show me the syntax for getting this
to work.

This is in Office '07 under Vista if that makes a difference.

TIA

Dave

Try this macro:

Sub copy_with_transpose()

Set copy_source = Worksheets("Sheet1").Range("A1")
Set copy_destination = Worksheets("Sheet2").Range("A1")

number_of_rows = copy_source.End(xlDown).Row - copy_source.Row + 1
number_of_columns = copy_source.End(xlToRight).Column -
copy_source.Column + 1

copy_source.Resize(number_of_rows, number_of_columns).Copy
copy_destination.PasteSpecial transpose:=True
Application.CutCopyMode = False
End Sub

Change the copy_source and copy_destination addressed to suit your
needs.

Hope this helps / Lars-Åke

 

[Risky Dave]

Lars-Ã…ke

Perfect!

My thanks

Dave

Try this macro:

Sub copy_with_transpose()

Set copy_source = Worksheets("Sheet1").Range("A1")
Set copy_destination = Worksheets("Sheet2").Range("A1")

number_of_rows = copy_source.End(xlDown).Row - copy_source.Row + 1
number_of_columns = copy_source.End(xlToRight).Column -
copy_source.Column + 1

copy_source.Resize(number_of_rows, number_of_columns).Copy
copy_destination.PasteSpecial transpose:=True
Application.CutCopyMode = False
End Sub

Change the copy_source and copy_destination addressed to suit your
needs.

Hope this helps / Lars-Ã…ke

 

[Vivek Trivedi]

Hi,

You can use the array function to do this task:

eg. Suppose your table is A1:C3 and you want to transpose it.

Use formula at the first cell of the desired location =transpose(A1:C3) then
select the required number of rows and columns for the data like in this case
3 rows and 3 colums. Now press F2 at the first cell of the block ie the cell
with the formula and press Clt+Shift+Enter Keys simultaneously .. and its
done .. you will see curly {}brackets before and after the formula. ..