Below is a vba solution that I posted yesterday to a very similar request to yours.
http://www.excelforum.com/excel-gene...e-records.html
Change input and output range parameters as required. You don't need to know how long the result list will be.
ExpandData.xlsm
Sub Expand_Click()
Dim Sht As Excel.Worksheet
Set Sht = ThisWorkbook.Worksheets("Sheet1")
ExpandData Sht.Range("B3:C5"), Sht.Range("B10")
End Sub
Public Sub ExpandData(SourceRng As Excel.Range, TargetCell As Excel.Range)
Dim Sht As Excel.Worksheet
Dim Arr As Variant
Dim i As Long, j As Long
Dim r As Long, c As Long
r = TargetCell.Row
c = TargetCell.Column
Set Sht = SourceRng.Worksheet
Arr = SourceRng.Value
For i = LBound(Arr, 1) To UBound(Arr, 1)
For j = 1 To Arr(i, 2)
Sht.Cells(r, c + 0).Value = j
Sht.Cells(r, c + 1).Value = Arr(i, 1)
Sht.Cells(r, c + 2).Value = Arr(i, 2)
r = r + 1
Next j
Next i
End Sub
LinkBack URL
About LinkBacks
hamidxa 
Register To Reply
Originally Posted by WHER
Bookmarks