1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82
| Option Explicit
Function IsGrouped(ByVal rngQuery As Range) As Boolean
IsGrouped = (rngQuery.EntireRow.Hidden And IsInGroupByRow(rngQuery)) Or _
(rngQuery.EntireColumn.Hidden And IsInGroupByCol(rngQuery))
End Function
Function IsInGroupByRow(ByVal rngQuery As Range) As Boolean
IsInGroupByRow = rngQuery.EntireRow.outlineLevel > 1
End Function
Function IsInGroupByCol(ByVal rngQuery As Range) As Boolean
IsInGroupByCol = rngQuery.EntireColumn.outlineLevel > 1
End Function
' Excel menu "Data" > "Group and Outline" > "Hide Detail"
Sub OutlineCollapse(ByVal rngQuery As Range, ByVal isByRow As Boolean)
Outliner rngQuery, isByRow, False
End Sub
' Excel menu "Data" > "Group and Outline" > "Show Detail"
Sub OutlineExpand(ByVal rngQuery As Range, ByVal isByRow As Boolean)
Outliner rngQuery, isByRow, True
End Sub
Sub Outliner(ByVal rngQuery As Range, ByVal isByRow As Boolean, ByVal isCollapse As Boolean)
Const typeByRow = 1, typeByCol = 2, sep = ","
Dim indType As Byte, indStart As String, indEnd As String
If isByRow Then
indType = typeByRow
indStart = rngQuery.Row - 1
indEnd = rngQuery.Row + rngQuery.Rows.Count - 1
Else
indType = typeByCol
indStart = rngQuery.Column - 1
indEnd = rngQuery.Column + rngQuery.Columns.Count - 1
End If
ExecuteExcel4Macro "SHOW.DETAIL(" & indType & sep & indEnd & sep & _
isCollapse & sep & indStart & ")"
End Sub
' Get the maximum of outline level between 1 and 255 by row even if groups are collapsed
Function OutlineLevelByRow(ByVal wsh As Worksheet) As Byte
Const nbrLevelMax = 255
Dim strAddress As String, indLevel As Byte, outlineLevel As Byte
Application.ScreenUpdating = False
outlineLevel = 0 ' Beyond nbrLevelMax nested outline levels
With wsh
strAddress = .Rows.SpecialCells(xlCellTypeVisible).Address
For indLevel = 1 To nbrLevelMax
.Outline.ShowLevels RowLevels:=indLevel
If .Rows.SpecialCells(xlCellTypeVisible).Address = strAddress Then
outlineLevel = indLevel
Exit For
End If
Next
End With
Application.ScreenUpdating = True
OutlineLevelByRow = outlineLevel
End Function
' Get the maximum of outline level between 1 and 255 by column even if groups are collapsed
Function OutlineLevelByCol(ByVal wsh As Worksheet) As Byte
Const nbrLevelMax = 255
Dim strAddress As String, indLevel As Byte, outlineLevel As Byte
Application.ScreenUpdating = False
outlineLevel = 0 ' Beyond nbrLevelMax nested outline levels
With wsh
strAddress = .Columns.SpecialCells(xlCellTypeVisible).Address
For indLevel = 1 To nbrLevelMax
.Outline.ShowLevels ColumnLevels:=indLevel
If .Columns.SpecialCells(xlCellTypeVisible).Address = strAddress Then
outlineLevel = indLevel
Exit For
End If
Next
End With
Application.ScreenUpdating = True
OutlineLevelByCol = outlineLevel
End Function |
Partager