VBA Text to Row with Row Insert ???

[Snaps]

Lately I've been running reports from our system that require partnerships that export in a single row to be split into their own separate rows. The names are always separated by a "/".

What I'm hoping to have happen is for a row to be inserted for every "/" that is in the A|B Columns. And then have the information in those cells split.

Example Before:

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=71>[B][FONT=Arial][SIZE=2]First Name[/SIZE][/FONT][/B]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 width=106>[B][FONT=Arial][SIZE=2]Last Name[/SIZE][/FONT][/B]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 width=107>[B][FONT=Arial][SIZE=2]Address[/SIZE][/FONT][/B]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>[FONT=Arial][SIZE=2]Jim/Tom[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]Stevenson/Smith[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]1432 Street Drive[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>[FONT=Arial][SIZE=2]Chris[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]Adams[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]4813 Road[/SIZE][/FONT]</TD></TR></TBODY></TABLE>

Example After:

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=284 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 53pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17 width=71>[B][FONT=Arial][SIZE=2]First Name[/SIZE][/FONT][/B]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 width=106>[B][FONT=Arial][SIZE=2]Last Name[/SIZE][/FONT][/B]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 width=107>[B][FONT=Arial][SIZE=2]Address[/SIZE][/FONT][/B]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>[FONT=Arial][SIZE=2]Jim[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]Stevenson[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]1432 Street Drive[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>[FONT=Arial][SIZE=2]Tom[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]Smith[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]1433 Street Drive[/SIZE][/FONT]</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=17>[FONT=Arial][SIZE=2]Chris[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]Adams[/SIZE][/FONT]</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">[FONT=Arial][SIZE=2]4813 Road[/SIZE][/FONT]</TD></TR></TBODY></TABLE>

Any help with this would be huge. My VBA experience is very limited but I'd love to figure it out.

 

[Warship]

Place this in a standard Module.
Change sheet names to match yours.

Sub NameSplit()
  Dim DataTable As Range, r As Long, s As Long, L As Long
  'set DataTable range without header
  Set DataTable = Sheets("Before").Range("A1").CurrentRegion
  Set DataTable = DataTable.Offset(1, 0).Resize(DataTable.Rows.Count - 1, DataTable.Columns.Count)
  'get results sheet ready
  Sheets("After").Cells.ClearContents
  Sheets("Before").Rows(1).Copy Sheets("After").Rows(1)
  L = 2
  For r = 1 To DataTable.Rows.Count
    'check if number of splits on first & last names are equal
    If Len(DataTable.Cells(r, 1)) - Len(WorksheetFunction.Substitute(DataTable.Cells(r, 1), "/", "")) _
      = Len(DataTable.Cells(r, 2)) - Len(WorksheetFunction.Substitute(DataTable.Cells(r, 2), "/", "")) Then
      'splits are equal - split data
      For s = 0 To Len(DataTable.Cells(r, 1)) - Len(WorksheetFunction.Substitute(DataTable.Cells(r, 1), "/", ""))
        Sheets("After").Cells(L, 1) = Split(DataTable.Cells(r, 1), "/")(s)
        Sheets("After").Cells(L, 2) = Split(DataTable.Cells(r, 2), "/")(s)
        Sheets("After").Cells(L, 3) = DataTable.Cells(r, 3)
        L = L + 1
      Next s
    Else
      'splits are NOT equal - leave data as is
      Sheets("After").Cells(L, 1) = DataTable.Cells(r, 1)
      Sheets("After").Cells(L, 2) = DataTable.Cells(r, 2)
      Sheets("After").Cells(L, 3) = DataTable.Cells(r, 3)
      L = L + 1
    End If
  Next r
End Sub

 

[Snaps]

Simply Amazing!

Thank you so much for your help.

Works perfectly.