Copying and Splitting Rows Horizontally

[Lynnmc26]

Hi there

I have a row with many columns. This is what I need to do;

In one of the cells there is multi values - eg

4376~4375~4374~4373

Instead of one row I need 4 rows that will have each of the above on its own row and all the data in the preceeding cells to copy down to the next rows as well.- so the result would be:

4376
4375
4374
4373

How can I get that to work without manually inserting the rows and copying the data manually then editing as well. Or is this possible?

 

[hiker95]

Lynnmc26,

Welcome to the MrExcel forum.


You did not say what column contained cells with ~'s.


Sample raw data before the macro:

Excel Workbook
	A	B	C	D	E	F
1	A1	B1	C1	D1	E1	F1
2	A2	B2	4376~4375~4374~4373	D2	E2	F2
3	A3	B3	C3	D3	E3	F3
4
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F
1	A1	B1	C1	D1	E1	F1
2	A2	B2	4376	D2	E2	F2
3	A2	B2	4375	D2	E2	F2
4	A2	B2	4374	D2	E2	F2
5	A2	B2	4373	D2	E2	F2
6	A3	B3	C3	D3	E3	F3
7
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub SplitData()
' hiker95, 04/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=541113
Dim LR As Long, a As Long, Sp
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 3).End(xlUp).Row
For a = LR To 1 Step -1
  If InStr(Cells(a, 3), "~") > 0 Then
    Sp = Split(Cells(a, 3), "~")
    Rows(a + 1).Resize(UBound(Sp)).Insert
    Rows(a).Resize(UBound(Sp) + 1).Value = Rows(a).Value
    Cells(a, 3).Resize(UBound(Sp) + 1).Value = Application.Transpose(Sp)
  End If
Next a
Application.ScreenUpdating = True
End Sub

Then run the SplitData macro.

 

[Peter_SSs]

WOW, that didn't take much adaptation!
http://www.mrexcel.com/forum/showthread.php?t=541095

 

[Lynnmc26]

Thank you - I will definitely give that a try - this is awesome and you get exactly what I need to happen - thanks for the thorough explanation. I will let you know if I have any other problems.

Lynn

 

[Lynnmc26]

Hi again

Well - I can see that will work if I had consistant three values in one cell, but if it looks like this: how would the code be written?

Col1 Col2 Col3 Col4 Col5
<TABLE style="WIDTH: 545pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=726><COLGROUP><COL style="WIDTH: 87pt; mso-width-source: userset; mso-width-alt: 4242" width=116><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 71pt; mso-width-source: userset; mso-width-alt: 3474" width=95><COL style="WIDTH: 240pt; mso-width-source: userset; mso-width-alt: 11702" width=320><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 87pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=116>test 1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=121>test 2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=74>test 3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 71pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=95>multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 240pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=320>23383</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>test 5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>test 6</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>test 7</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>2238383</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>jane</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>jack</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>lisa</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>22333~339393~30930</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>another</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>one</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>to</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>88999~9900333</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20>testing</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>this one</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>again</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>multi-part</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64>6564393~20110032333~201100330~393938</TD></TR></TBODY></TABLE>

 

[Lynnmc26]

Actually I got it - it works and thanks again!

 

[Armster]

How do I get this to work with splitting a multiline cell (not merged) in to rows between two sheets? I have two Range's setup for the respective area's but cna't seem to work out how to do it (haven't had much to do with macro's/scripting for a few years lol) I need this to populate a form from one cell (as that gets updated through a mail merge style document I'm making

My Name Ranges are:

AutoDeskSW (One Cell for source)

AutoDeskSWFormRange (a set of 5 cells in a form, so it can't be an insert must be a copy)


Is this possible?


A Sample of the different single Cell contents are as follows:
"
Revit Structure
DWG Trueview
Design Review
"

"
• Revit MEP Viewer
• Revit Structure
• 3dSM"

"
No additional Software Requested
"

 

[hiker95]

Armster,

Please start your own NEW post.


When you do we will need:

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:


Then you can send me a Private Message containing a link to your NEW post, and I will try to solve your request for you.