Macro to transpose multiple rows to a single column

You will need to define a stop condition.

Put it another way: if single cells can remain blank, what happens if the first cell in the row is blank?
Do you want the macro to expect a selected range? or should it stop in case EACH cell in the current row is blank?

Alright, I gave it a go.

The Sub below will look through rows until all cells in columns 1 to 25 are empty, storing the values in an array and outputting that to a new worksheet.
Blank cells equal blank rows in the output.

Hope that's the solution you were looking for

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Hey Chris,

Sorry it's taken me so long to get back to this. I should be better at responding and following up going forwards! So this works well except for one thing... is it possible to paste the original formats too? My issue is that in one of the cells, I have a relatively long number which I've stored as text (otherwise it screws up the number), and when it copies it to the new sheet the formatting gets messed up. e.g. 0660011441633 on my original sheet becomes 6.6E+11 on the new sheet (that's what I mean by the number gets screwed up)

Thanks!

Hey John,

Thanks for that. For the most part it works well and with some manual intervention it does the job. Just by way of feedback:
1. If there is no sheet called Sheet2 then your code doesn't work
2. It only starts pasting from the 2nd row down instead of from the 1st row
3. It keeps the last row of the original sheet 'copied' rather than ending the copy function

Other than that, it does the job.

Cheers

In reply to your formatting issue, TMS has given the exact answer: I used an array for performance reason, but there's no reasonable way to retain the original formatting with that. If that's a must, you should probably stick with the other solution and adapt that to your needs.
As is the problem with most IT tasks: clear definition of requirements yields the best results. But if the requestor knows how to phrase those beforehand, he is usually sufficiently skilled to perform the task himself.

Thanks Chris. I did notice that your code executed a lot faster than John's and I guess now I understand why. Pity about the formatting though, I just don't understand why Excel massacres the number like that, it's really annoying. Thanks anyways!

Hi TMS,

1. Answer to your question is 'yes'
2. I'll attach a sample workbook for you - just need to figure out how to do that
3. Agreed, though I didn't think at the time about the formatting issue
4. I did say in my initial request

a Macro that will reformat the data and paste it on a new sheet?

therefore I would assume that it becomes irrelevant as to what worksheets exist.
5. I never asked for the destination sheet to have a header row, I just asked for the date to be transposed from one sheet to the next, therefore no header row would naturally exist
6. Fair enough, I was unaware that this was such a big issue. I have never posted on forums before.
7. Understood
8. Ok
9. Yes I did, and now I'm following up to see if any tweaks can be made that will provide me with the best end product

Originally Posted by TMS

Maybe this slight modification to ChrisVBA's code:

Please Login or Register  to view this content.

Regards, TMS

Of course *facepalm* - since it's apparently ok for him to have _SOME_ of the cells in number format, why shouldn't they _ALL_ be?
I was thinking too complicated... :D

Cheers

Chris

That's brilliant! Works like a charm!
Thank you TMS (and Chris) for all your help, it's greatly appreciated!

Didn't realise at first there a 2nd page to this thread. Just checked them out and they're all good... the last one is probably the most ideal for what I need.

Thanks a stack TMS! And I've taken note of your comments on forum education!

Cheers