Macro to split cells into inserted new row and copy the rest of the row into it.

[e01nd]

Hi,

I really hope someone can help me?

I need a macro that can search text cells in column L for a space delimiter, then if the space is present, to split that cell into a row inserted below it, then copy the information in the rest of the row down. I'm not really up on my VBA and this is pretty complicated!

It's for a large range of data, so can't really be done manually with transpose.

Thanks in advance!

[Paul]

Hello e01nd,

I think the code below will do what you're requesting. Place it into the worksheet's vbe module (right-click on the sheet tab in question, click View Code, paste this code into the empty white space on the right of the VBE window). You can then run the code via the macros menu or by pressing ALT+F8.

Please Login or Register  to view this content.

[Paul]

A slightly cleaner version using variables instead of repeating the same functions:

Please Login or Register  to view this content.

[Trebor76]

Hi there,

Assuming the data is continuous from cell L2, the following should do the trick.

HTH

Robert

Please Login or Register  to view this content.

[e01nd]

Hi Both,

Thanks for these. Trebor, your code seems to work best! Would it be possible to copy down the data in the rest of the row too?

Many thanks for your time and energy!

[Trebor76]

Hi there,

Glad to hear it worked (almost).

Is there a certain column where the data always stops at or is it variable (if it's variable, is the data continuous across the sheet)?

Regards,

Robert

[e01nd]

Hi Robert,

The last column is N.

Thanks a million!!

[Trebor76]

Hi there,

I've made the following code dynamic (it finds what the last column with data in it is) and therefore more flexible.

Let me know how it goes.

Robert

Please Login or Register  to view this content.

[e01nd]

Hi Robert,

Again, that works great. Only problem is that it's not copying down the data before column L, only data afterwards.

I'm amazed at how quickly you're churning these out. Your help is really appreciated!

[Trebor76]

Hi there,

Hopefully this should be it.

Regards,

Robert

Please Login or Register  to view this content.

[e01nd]

Works perfectly!!!

Thank you so much!!

[Trebor76]

You're very welcome!!

[Reafidy]

Another way:

Please Login or Register  to view this content.

[Paul]

The SPLIT function!!! ::smacking self in head::



Nice work!

[lmtwashington]

I'm using this, and it's great if there are only 2 items in the cell. What if there are 1-5 delimited values in the cell/range? My spreadsheet has columns A-AR, Rows 1-29 (for now). In Column R, I have multiple delimited values. I'd like to add a row for each one, but leave other cells from other columns blank. What I'd really like is to be able to split multiple columns, as I have this happening in a couple of columns. Any help? Thanks!

[arlu1201]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.