Private Sub Worksheet_Activate()
Dim F As Worksheet, lig1&, lig2&, col1%, col2%, col3%, col4%, col5%
Dim ncol%, tablo, resu(), i&, j%, n&
'---à partir des noms définis---
Set F = [ID].Parent
lig1 = [ID].Row
lig2 = [Sup_100].Row
col1 = [ID].Column
col2 = [Nom_1].Column
col3 = [Nom_2].Column
col4 = [Sup_100].Column
For col5 = col4 To F.Columns.Count
If Val(Replace(CStr(F.Cells(lig2, col5)), ",", ".")) < 100 Then Exit For
Next col5
col5 = col5 - 1
'---définition des tableaux---
With F.Range("A1", F.UsedRange)
ncol = .Columns.Count
If ncol = 1 Then ncol = 2 'sécurité, au moins 2 éléments
tablo = .Resize(, ncol) 'matrice, plus rapide
End With
ReDim resu(1 To F.Rows.Count, 1 To 5)
'---remplissage du tableau des résultats---
For i = lig1 To UBound(tablo)
If Not IsNumeric(CStr(tablo(i, col1))) Then Exit For
For j = col4 To col5
If IsNumeric(CStr(tablo(i, j))) Then
n = n + 1
resu(n, 1) = tablo(i, col1)
resu(n, 2) = tablo(i, col2)
resu(n, 3) = tablo(i, col3)
resu(n, 4) = tablo(lig2, j)
resu(n, 5) = tablo(i, j)
End If
Next j, i
'---restitution---
If FilterMode Then ShowAllData 'si la feuille est filtrée
With [A2] 'à adapter
If n Then .Resize(n, 5) = resu
.Offset(n).Resize(Rows.Count - n - .Row + 1, 5).ClearContents 'RAZ en dessous
End With
With UsedRange: End With 'actualise la barre de défilement verticale
End Sub