Moved wrapped lines to individual cells.

Putting this here because I can't figure a way to do it any other way, but of course I COULD be wrong. Maybe I'm just too slow to figure it out.

Okay, in the attachment my boss put in his info in Column B. Now I'm stepping it out in column D

My bosses comments are wrapped, but the steps don't line up neatly with his steps, and I'd like to have each of his lines (and so mine as well) in a separate cell, but staying in the same column with the same width.

So, for instance, his box for Projected Paid FTEs is currently occupying three cells (B4:B6). When all's said and I done it would occupy seven cells (B4:B10)

Without manually copying/pasting, how can I do this? I tried with Text to Columns, but that moves everything to different columns, so I still have to copy and paste.

What's the solution? I'd kind of like a macro solution, because this same situation happens all the time (I have a boss who loves Merging and Wrapping).

Any help would be appreciated.

I think you are going to struggle. The only reason the text in a cell takes up however many lines is because of the column width. If you narrow it or increase the column width, it will increase or decrease the number of lines. So, the number of lines is just a function of the formatting. If you had new line characters in there, you could split the cell ... but you (and your boss) would need to press Alt-Enter to force the line break(s).

Maniacb I've attached an updated sample. TMS, yes, it would definitly make a difference if I changed the column width, what I want to do is keep the same column width and convert each wrapped line to it's own line.

In many cases I or my boss DO use line breaks, but not always. If the text wraps the way we want without a line break so much the better.

But regardless of line breaks or not, wrapped text multiplies the row height, causing difficulty lining up comments on the side. Easier with single lines.

Here is an option for you. Let us know how it works.

Please Login or Register  to view this content.

That worked very well. Some minor formatting issues (some indents lost, some retained). I've attached the test results. I'll have to examine this macro in detail. Thanks so much!

Some questions on the macro:
What is the 110 value about?
You define the column width, but if I were wanting to turn this into an on-the-fly macro I could select the text to unwrap, so capture the size that way, right?
Same thing with calling out column B, selection would take care of that as well, right?
I think all the restoring the formatting (like bolding, borders) I could take out. That's easily restored manually, and if it's lost during the unwrapping process that's not a deal breaker.

One thing I'm not sure about are lines that begin with [B3], like so:

Please Login or Register  to view this content.

Is it B3 because that's where the data begins?

The 110 value defines how many characters fit in a cell that is 68.77 long. Right now the code uses a set column width and character count, but that can be changed for the code to read the column width and update the character count accordingly. Changing the letter B will update the column, although I have to make one update in the code for that to be true. B3 in those lines do designate where the data is populated into. *The update to the code to change B for the column change has been made.

I'm not seeing where you made the change. Unfortunately I need to get away from this post for a day or so while I work on a pressing issue but I will be back.

When you get back, here is another module you can use to break down a single cell into multiple rows. It will only work for font sizes between 8 and 16. The code in post #5 is currently set up to work on font size 10. I also made a correction to it. Let me know what you think.

Please Login or Register  to view this content.