Showlevels (Excel VBA)

Topic

New Reply

I have some code that shows data to a certain level in an Excel sheet…

ExcelObject .ActiveSheet.Outline.ShowLevels RowLevels:=4

4 used to be the second to last level of detail, 5 would display everything in the report. However, the report being produced is now changing regularly & 4 is not always the correct level to display.

What I need to do is determine the second to last level of detail in the report & ShowLevels to this …. eg;

Dim iLevels as Integer

iLevels = ExcelObject.ActiveSheet.Outline.{GetLevels}
ExcelObject .ActiveSheet.Outline.ShowLevels RowLevels:=iLevels

Is there anything like this, or am I in dreamworld here….?!

Thanks

Reply | Quote

Replies

How about this:

Sub test()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    MsgBox GetLevels(oSh)
End Sub

Function GetLevels(oSh As Worksheet) As Integer
    Dim iLevels As Integer
    iLevels = 0
    Do
        oSh.Outline.ShowLevels iLevels + 1
        If oSh.Cells.SpecialCells(xlCellTypeVisible).Count  oSh.Cells.Count Then
            iLevels = iLevels + 1
        Else
            Exit Do
        End If
    Loop
    GetLevels = iLevels
End Function

Reply | Quote

Thanks, I’ll give this a go…

Reply | Quote

Thanks, I’ll give this a go…

Reply | Quote

How about this:

Sub test()
    Dim oSh As Worksheet
    Set oSh = ActiveSheet
    MsgBox GetLevels(oSh)
End Sub

Function GetLevels(oSh As Worksheet) As Integer
    Dim iLevels As Integer
    iLevels = 0
    Do
        oSh.Outline.ShowLevels iLevels + 1
        If oSh.Cells.SpecialCells(xlCellTypeVisible).Count  oSh.Cells.Count Then
            iLevels = iLevels + 1
        Else
            Exit Do
        End If
    Loop
    GetLevels = iLevels
End Function

Reply | Quote