Sub ash_cacheLV_SUMPRODUCT_F2()
Dim plage As Range
Dim t0, n%
'--définition de la plage de recherche
Set ash = Sheets(2) 'ActiveSheet
Set plage = ash.Range("G2:G19,G27:G41")
t0 = Timer
On Error Resume Next
'--boucle 1
With plage
.Formula = "=1/(1/SUMPRODUCT(N(D2:E2<>0)))"
End With
'--boucle 2
For Each plage In ash.Range("G2:G19,G27:G41").Areas
' Travaille sur la plage en cours
With plage
.Value = .Value
n = Application.Count(.SpecialCells(xlCellTypeConstants, 16))
.SpecialCells(xlCellTypeConstants, 16).EntireRow.Hidden = True
.Value = ""
End With
Next
With ash.UsedRange: End With 'actualise les barres de défilement
MsgBox Format(Timer - t0, "0.00\ sec." & vbCrLf & n & " lignes cachées")
End Sub