Ungroup the group and outline to a single cell

[caerdydd]

Hello
I am trying to work out a method to concatenate the data within a number of group and outline branches to a single cell.
As an example to make it a little more understandable...the data is arranged in the group and outline as

1
1
1
2
2
3
2
3
3
4
5

The way i want to get this to be concatenated is so that in cell A1 the results would be 1,1,1 and then in A2 - 1,1,2 then in A3 - 1,2,3 then A4 - 2,3 then A5 - 3,4,5.
Below is how i have tried to get this going but having problems with looping back to the original top level node.

===================================
Sub Product_View_to_Data_View()
Dim sCell As String
Dim mycell As Excel.Range

'ThisWorkbook.Sheets.Add
i = 0
c = 1

With ThisWorkbook.Sheets(1).Cells
Set mycell = .Range("A6")
sCell = .Range("A6").Offset(i, 0).Value

If sCell <> "" And mycell.Interior.ColorIndex = 4 Then
myval = sCell & ","

Do Until mycell.Offset(i + 1, c) <> ""
c = c + 1
Loop
myval = myval & mycell.Offset(i + 1, c) & ","
i = i + 1

Do Until mycell.Offset(i + 1, c) <> ""
c = c + 1
Loop
myval = myval & mycell.Offset(i + 1, c) & ","

End If
End With

End Sub
===============================

Hope this is understandable but any further clarification i would be happy to forward.
Thanks & regards

 

[SkipVought]

Hi,

"...looping back to the original top level node"

WHY?

What you want to do is assign the higest level value to a variable, until you encounter a new value. No "looping back"

Use this kind of technique...

  with thisworkbook.sheets(1).usedrange
    lLastRow = .row + .rows.count - 1
  end with
  Do Until i >= lLastRow
     if iPrev = mycell.Offset(i, "A").value then
        'while the highest level has not changed

     else
        'when the highest level changes

     end if    

     iPrev = mycell.Offset(i, "A").value
     i = i + 1
  Loop

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}