Private Sub CommandButton1_Click() 'Valider
Dim c As Range
For Each c In [F13:F21] 'plage à adapter
c.EntireRow.Hidden = IsEmpty(c)
Next
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim F As Worksheet, i&, j As Variant
Set F = Sheets("Liste (à masquer)")
Application.ScreenUpdating = False
Application.EnableEvents = False 'désactive les évènements
With [B13:F21] 'plage à adapter
If Not Intersect(Target, [C5]) Is Nothing Then
.Rows.Hidden = False 'affiche tout
.ClearContents 'RAZ
F.[A:E].Clear 'RAZ
With Sheets("Articles").[A1].CurrentRegion
.Sort .Columns(3), xlAscending, Header:=xlYes 'tri alphabétique
.AutoFilter 1, [C5] 'filtre automatique
.SpecialCells(xlCellTypeVisible).Copy F.[A1]
If .Parent.FilterMode Then .Parent.ShowAllData
End With
ThisWorkbook.Names.Add "Liste", "=#REF!"
With F.[A1].CurrentRegion
If .Rows.Count > 1 Then .Cells(2, 3).Resize(.Rows.Count - 1).Name = "Liste" 'plage nommée
End With
End If
If Not Intersect(Target, .Cells) Is Nothing Then
For i = 1 To .Rows.Count
j = Application.Match(.Cells(i, 2), F.Columns(3), 0)
If IsNumeric(j) Then .Cells(i, 1).Resize(, 4) = F.Cells(j, 2).Resize(, 4).Value2 _
Else .Cells(i, 1).Resize(, 4) = ""
Next
End If
End With
Application.EnableEvents = True 'réactive les évènements
End Sub