Transposing column range to row while excluding blanks

Hi,

This may end up being a bit of a noob question, but I'm just getting started with VBA.

I have a worksheet full of data that has been formatted for ease of viewing, but from which I need to pull data into a more suitable format for analysis and visualization in Spotfire. To do this, I need to copy non-blank cells from a source column and transpose these cells into a contiguous row in a different worksheet. In the long run there will be a lot more logic behind this operation, but this is just a first step.

I hacked around a bit and have come up with the following code, which successfully accomplishes my task:

Please Login or Register  to view this content.

My question relates to speed and elegance of the solution. Because it uses a for loop to go through all the cells the operation is fairly slow. Eventually this operation may be applied multiple times at the push of a button, leading to a loop that cycles through and copies literally thousands of cells - very time consuming.

It seems like there should be a more elegant way to handle this. Something along the lines of copying the entire range as a single item (rather than looping through each cell) and removing the blanks along the way.

I've attached the sheet I'm working with as an example. Any suggestions will be greatly appreciated.
Example _ transpose without blanks.xlsm

Thanks in advance,
James

Hi Martin,

Thank you for the feedback. That solution loses the number formatting, but I noticed I can get around that by pre-formatting the destination cells.

The trouble is it is only marginally faster (only shaves a couple seconds off the operation).

If you have any other suggestions I'd certainly be grateful.

Thanks,
James

Hi -

Please Login or Register  to view this content.

before: 5.0313 seconds
after: 1.4844 seconds

Regards,
Event

Hey Event & mrice, thank you for the extra information.

In terms of speed, shutting off the screenupdating and cutcopymode certainly helped. A User on another forum was able to suggest this for me however, which has really taken the cake:

Please Login or Register  to view this content.

Thanks for all the help!
James