[Solved] VBA Excel tricky “Nested” Column Sorting [closed]

by


If I was coding this myself (rather than trying to explain a method for someone else to code this) I would do the following:

Type ValueAndLevel
    dataValue As String
    dataLevel As Long
End Type

Dim myData() As ValueAndLevel

Sub Export1()
    ReDim myData(1 To 1)
    'Start the process in column 1, with level being 0
    Process 1, 0
    'Get rid of the last dummy entry added to the array
    ReDim Preserve myData(1 To UBound(myData) - 1)
End Sub

Sub Process(c As Long, l As Long)
    Dim vl As ValueAndLevel
    Dim r As Long
    Dim c1 As Long
    'Start this column at row 2
    r = 2
    Do While Cells(r, c).Value <> ""
        'Store this cell's details
        vl.dataValue = Cells(r, c).Value
        vl.dataLevel = l
        myData(UBound(myData)) = vl
        'Increase size of array ready for next value
        ReDim Preserve myData(1 To UBound(myData) + 1)
        'search for sublevels starting from the column to the right
        c1 = c + 1
        Do While Cells(1, c1).Value <> ""
            If Cells(1, c1).Value = Cells(r, c).Value Then
                'If this column's row 1 matches the value we are looking for,
                'process the column (at a level one deeper than we were at)
                Process c1, l + 1
                'Don't look for any more matching values - assume only one per customer
                Exit Do
            End If
            c1 = c1 + 1
        Loop
        'Get ready to process the next row in this column
        r = r + 1
    Loop
End Sub

At the end of Export1 having run, the myData array will contain the relevant values and their associated levels.

1

solved VBA Excel tricky “Nested” Column Sorting [closed]