Transpose OR Rotate a 2D array ( excel ) 1

[shavenlunatic]

Hi,

Looking for an alternative to using pastespecial transpose here. I am trying to avoid scanning each cell and passing the values accross

The two methods I am hoping are possible are as follows:

take this data as an example

  A B C D
1 t h i s
2 d a t a
3 n e e d
4 s a i d

First method:

Load this data into a 2D array and rotate the array, then place the data on a sheet in the new arrangement (not sure if there is a function to rotate an array, but I am trying to avoid scanning each part of the array and doing basically the same as I mentioned above with the cells)

Second Method

Grab the range of data (would even be happy doing it row-by-row) and transposing it into another sheet, but avoiding using pastespecial for the obvious reasons.



I doubt if either of these are possible and I expect I am going to have to loop through all of the data and do it cell by cell, but I was hoping to find a "neater" method.


Any help appreciated and thanks in advance


________
clueless

 

[shavenlunatic]

To add to this, I tried doing

sheet2.range("A1:D1") = sheet1.range("A1:A4")

It doesn't generate an error, but it simply doesn't do anything

________
clueless

 

[GlennUK]

Why are you avoiding using PasteSpecial Transpose "for the obvious reasons" exactly? ( what "obvious reasons"???? )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[shavenlunatic]

pasting in a script is (IMO)

a) Dirty
b) Prone to problems, e.g. someone has this running in the background, hits ctrl+c on a bunch of text and the script pastes this text in to the sheet, as this is all happening in the background, automatically saving and automatically getting e-mailed, it won't be noticed until the recipient opens the report and notices "LOL wot r u doin tonite?!?!?" where some data should be becuase some idiot decided to copy/paste part of an e-mail they were sending their friend while waiting for the reports to run.

See my point?



________
clueless

 

[GlennUK]

I don't know what you mean by "dirty" ... although I see what you mean about problems with using the clipboard when it's running in background ( although that isn't exactly an obvious problem ).

So, you "obviously" want to avoid using PasteSpecial/transpose. What about using the Transpose worksheet function ( in your VBA code )?





Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[shavenlunatic]

well, as I seem to have upset you with my use of the word "obviously" and my lack of willing to use pastespecial, I apologise. But I feel that using copy/paste is just a quick and dirty was of doing things that given half a seconds thought will only cause problems. Same with activesheet and other things, I just don't like leaving things to chance, I guess thats just me...

The transpose function doesn't seem to make sense (to me) when running it in VBA, but it doesn't matter now as I ended up conceding and looping through each cell and manually transposing the data, with automatic calculations turned off it only takes a few seconds so I can live with that.

Cheers anyway

________
clueless

 

[GlennUK]

Your use of the word "obvious" didn't upset me ... honest! I just wanted to know what was obvious to you, in case it was something different to what was obvious to me.

I'm glad you got your VBA working, but just for the record, this is what I meant by using the Transpose function:

    Sheet1.Range("A1:D4") = Application.Transpose(Sheet1.Range("A1:D4"))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[shavenlunatic]

ooh, lovely. That worked on my test model. Will give it a go on the larger sheet after a meeting.

Cheers Glenn

________
clueless

 

[shavenlunatic]

woo, it worked. it worked..... obviously



Have a star

/click

________
clueless

 

[GlennUK]

Great! Glad that has helped you. :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.