none
How to query group size or end of group in Excel (JS/VBA)? RRS feed

  • Question

  • Hello,

    I am writing a HTA which can show data in Excel.

    The Excel file may have groups on rows, it is bad to iterate all rows with Range.OutlineLevel to represent the groups because there are lot of data. To show root rows like a tree control doing is a better solution.

    I wander if there is a way to query group size or end of group.

    Like:

    if ( ActiveSheet.Rows(2).OutlineLevel >1 )

    {

      var size = ...;

      var end = ...;

    }

    THX.

    Monday, June 8, 2015 6:17 AM

Answers

  • Hi

    As far as I know, Outline object doesn’t provide a method or property that could get the size of the group. But as a workaround. you may get the outline level for every used row, and calcite to get the size of group.

    Here is a demo, which would get the group scope for the outlinelevel is 3.you could adapt it to satisfy your requirement.

    http://1drv.ms/1IuFsoX

    Sub GetScope()
    Dim GroupRange As Range
    Dim GroupScope As String
    Set GroupRange = Range("A1:D12")
    For i = 2 To GroupRange.Rows.Count
    If Rows(i - 1).OutlineLevel <> 3 And Rows(i).OutlineLevel = 3 Then
    GroupScope = GroupScope & "   Start : " & i
    End If
    If Rows(i).OutlineLevel = 3 And Rows(i + 1).OutlineLevel <> 3 Then
    GroupScope = GroupScope & " End : " & i & vbNewLine
    End If
    Next i
    For Each myrow In GroupRange.Rows
    If myrow.OutlineLevel = 3 Then
    RowCount = RowCount + 1
    End If
    Next
    MsgBox GroupScope & vbNewLine & "Rowcounts : " & RowCount
    End Sub

    Hope this could help you.

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by L.HlModerator Tuesday, June 16, 2015 1:58 AM
    Wednesday, June 10, 2015 9:03 AM
    Moderator