Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
If [filtrage] Then
Cancel = True
Rows("5:65536").Hidden = False
Columns("V:IV").Hidden = False
Range("O1,Q:U").EntireColumn.Hidden = False
Application.Goto Target, True 'cadrage de la cellule
Me.Names.Add "filtrage", False
Else
Dim c, col%, plage As Range, filtre As Range
c = Cells(Target.Row, "P") 'compétence
col = Cells(4, Columns.Count).End(xlToLeft).Column 'n° dernière colonne
If c = "" Or _
Intersect(Target, [V5].Resize(65532, col - 21)) Is Nothing Then Exit Sub
Cancel = True
Application.ScreenUpdating = False
'---filtre élaboré (avancé)---
Set plage = Rows("4:" & [B65536].End(xlUp).Row)
[A5].Formula = "=P5<>" & c
plage.AdvancedFilter xlFilterInPlace, [A4:A5]
Set filtre = plage.Offset(1).SpecialCells(xlCellTypeVisible)
plage.AdvancedFilter xlFilterInPlace, "" 'désactivation du filtre
'---1er masquage : des lignes filtrées---
filtre.EntireRow.Hidden = True
'---2ème masquage : des colonnes autres que celle de Target---
Columns("V").Resize(, col - 21).Hidden = True
Target.EntireColumn.Hidden = False
'---3ème masquage : des lignes si pas d'absence affichée---
Intersect(plage.Offset(1), Target.EntireColumn) _
.SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
Target.EntireRow.Hidden = False 'si Target est vide
'---autre mise en forme---
Range("O1,Q:U").EntireColumn.Hidden = True
ActiveWindow.SmallScroll Up:=65536
Me.Names.Add "filtrage", True 'mémorisation
End If
End Sub