Macro to transpose multiple rows to a single column

[Gup]

Hi,

Before:
a1 b1 c1 d1 e1
a2 b2 c2 d2 e2
a3 __ c3 d3 __
a4 b4 __ d4 e4

After:
a1
b1
c1
d1
e1
a2
b2
c2
d2
e2
a3

c3
d3


a4
etc etc

The number of columns in my data is static at 25, but the number of rows can vary. If there are blank cells in the columns, it should leave a blank row in the transposed data, as per my example above.

Can you please help me out here with a Macro that will reformat the data and paste it on a new sheet?

Thanks

[TMS]

So, if F1 to Y1 is blank, do you want 20 blank rows in the column?

Regards, TMS

[ChrisVBA]

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?

[ChrisVBA]

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.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[Gup]

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!

[Gup]

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

[TMS]

By way of feedback?

1. You didn't reply to my question
2. You didn't provide a sample workbook
3. Your "sample data" in the first post gives no indication of the true content or format of your data
4. How would anyone know what worksheets exist?
5. How would anyone know if your destination worksheet has a header row
6. Although you have apologised, it's still over a week since you posted, whereas the replies came within hours
7. Using an array to store the data in transition will be quick but it won't retain formatting
8. What you asked for:

Can you please help me out here with a Macro that will reformat the data and paste it on a new sheet?

9. From your replies, that's what you got x 2

Regards, TMS

[ChrisVBA]

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.

[Gup]

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!

[Gup]

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

[Gup]

Export File - Copy.xls

Here is a sample of the workbook that the Macro is for, which contains fictitious data. The top row does not need to be transposed to the new sheet, but that can also just be deleted before running the macro.

The issue lies with column M - those are the numbers that display incorrectly if the formatting is not maintained.

e.g. 0660011441633 on my original sheet becomes 6.6E+11 on the new sheet

[TMS]

4.

If there is no sheet called Sheet2 then your code doesn't work

6. Not necessarily a big deal but the people answering the question(s) enjoy the challenges and welcome the feedback. If nothing happens for a week, it feels a bit like you've asked a question and you're not interested in the answer(s). I know that people are busy and have other, sometimes unexpected commitments but, if that's the case, it's helpful just to post a "holding" note ... maybe, "not forgotten, just tied up, will pick up in a few days"

Might be worth marking this thread as closed, starting a new thread with the sample data, and linking back to this thread.

That way, you'll generate new interest, maybe get some different solutions, have the current solutions for reference, etc.

Regards, TMS

[TMS]

Maybe this slight modification to ChrisVBA's code:

Please Login or Register  to view this content.

Regards, TMS

[ChrisVBA]

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

[TMS]

@ChrisVBA:

I was thinking too complicated

Maybe not, let's see. Could perhaps also right align the cells, then they'll still look like numbers ... only potential issue being arithmetic.

Regards, TMS

[TMS]

Ah, no, there are text fields that would need to be left aligned. Oops.

[TMS]

And a slightly modified John H Davis code:

Please Login or Register  to view this content.

This creates a new worksheet in the same way that ChrisVBA's code does. But it doesn't select it.


Regards, TMS

[TMS]

Option 3: based on John's code with Chris's worksheet add embellishment.

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Little tweak at the end ...

Please Login or Register  to view this content.

Regards, TMS

[Gup]

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

[Gup]

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

[TMS]

You're welcome.