I need some macro help

M

MSteve

New Member
Joined
Apr 4, 2011
Messages
3
This looks like a great forum and I'm glad I found it.

I've searched for a few hours trying to find a solution for my issue but no luck. I'm pretty good with formulas and can easily do this with PHP and MySQL but I need to do it with Excel.

Maybe this forum can help. I have a spreadsheet similar to what I've posted below. It's more complicated with a lot more data but that's the basic concept. It ends up with rows of data and the last cell in each row needs to be split.

Code: 
Joe	Blow	Dog
Jane	Dow	Cat|Dog|Mouse
John	Smith	Cat

It's easy enough to use the Text to Column feature to split that cell but they want a new row for each new entry in that cell and that's where a macro is needed so they end up with something like this.

Code: 
Joe	Blow	Dog
Jane	Dow	Cat
Jane	Dow	Dog
Jane	Dow	Mouse
John	Smith	Cat

I certainly don't mind having them use the Text to Column before running the macro but it's just not an option for me to do this outside of Excel.

Any ideas to help this lost newbie? :confused:
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
MSteve,

Welcome to the MrExcel forum.


Sample raw data before the macro:


Excel Workbook
ABC
1JoeBlowDog
2JaneDowCat|Dog|Mouse
3JohnSmithCat
4
Sheet1





After the macro:


Excel Workbook
ABC
1JoeBlowDog
2JaneDowCat
3JaneDowDog
4JaneDowMouse
5JohnSmithCat
6
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.


Code: 
Option Explicit
Sub SplitData()
' hiker95, 04/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=541095
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 + 1).Resize(UBound(Sp)).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.
 
Upvote 0
That was perhaps the most detailed and accurate response I've gotten from any forum and I've been doing this for more than a decade. It works great!

THANK YOU! :biggrin:
 
Upvote 0
MSteve,

You are very welcome.

Thank you very much. Glad I could help.

Come back anytime.
 
Upvote 0
One note worth mentioning about this macro which was obvious to me but may not be so obvious to others, is "3" appears in 4 spots. This should be replaced with the column number that contains the data that needs to be split.

And it works even if the column is in the middle of the data :biggrin:
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Automate Attendance Marking Based on Daily Schedule in Excel
Replies
1
Views
243
Peter_SSs
Peter_SSs
B
  • Question Question
VBA to show and change list of names depending on selection
Replies
7
Views
749
mumps
mumps
J
  • Question Question
Find line break in cell, insert in cell below
Replies
5
Views
391
JvdV
J
J
  • Question Question
Macro to pull Office 365 'Comments' onto new tab like I previously did with what's now 'Notes'
Replies
1
Views
131
jkouvel
J
J
  • Question Question
Macro to adjust cell heights with wrapped text
Replies
2
Views
863
Jest
J

Forum statistics

Threads
1,224,518
Messages
6,179,259
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top