Breaking a row into multiple rows
 

I have a table like this:

Name ID Type
A 12; 14 New
B 13; 15 Old

I want to convert this to:

Name ID Type
A 12 New
A 14 New
B 13 Old
B 15 Old

Breaking a row into multiple rows
 

Dim cell As Range
Dim rng As Range
Dim v As Variant, i As Integer
Dim wksNew As Worksheet
Set rng = Range("B2:B4")
Set wksNew = Workbooks.Add(1).Worksheets(1)
For Each cell In rng.Cells
v = Split(cell.Value, ";")
For i = LBound(v) To UBound(v)
With wksNew.Cells(65000, "A").End(xlUp).Offset(1)
.Cells(1, 1).Value = cell.Offset(, -1).Value
.Cells(1, 2).Value = Trim(v(i))
.Cells(1, 3).Value = cell.Offset(, 1).Value
End With
Next
Next

Adjust the rng setting to be the proper semicolon-delimited list.

--
Tim Zych
SF, CA


"Yoss-22" wrote in message
...
I have a table like this:

Name ID Type
A 12; 14 New
B 13; 15 Old

I want to convert this to:

Name ID Type
A 12 New
A 14 New
B 13 Old
B 15 Old

Breaking a row into multiple rows
 

Thanks, that helps.

"Tim Zych" wrote:

Dim cell As Range
Dim rng As Range
Dim v As Variant, i As Integer
Dim wksNew As Worksheet
Set rng = Range("B2:B4")
Set wksNew = Workbooks.Add(1).Worksheets(1)
For Each cell In rng.Cells
v = Split(cell.Value, ";")
For i = LBound(v) To UBound(v)
With wksNew.Cells(65000, "A").End(xlUp).Offset(1)
.Cells(1, 1).Value = cell.Offset(, -1).Value
.Cells(1, 2).Value = Trim(v(i))
.Cells(1, 3).Value = cell.Offset(, 1).Value
End With
Next
Next

Adjust the rng setting to be the proper semicolon-delimited list.

--
Tim Zych
SF, CA


"Yoss-22" wrote in message
...
I have a table like this:

Name ID Type
A 12; 14 New
B 13; 15 Old

I want to convert this to:

Name ID Type
A 12 New
A 14 New
B 13 Old
B 15 Old