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
| Option Explicit
Sub ListerNombreDeProduits()
Dim FL1 As Worksheet, NB As Integer
Dim Valeur() As String, c As Range, i As Integer, j As Integer
Dim NoLigne As Long, DerLig As Long
Set FL1 = Worksheets("Feuil1")
NoLigne = 2 'en supposant que les noms de produits commencent à la ligne 2
Do
If Not Cells(NoLigne, 1) = "" And Not Rows(NoLigne).Hidden Then
i = i + 1
ReDim Preserve Valeur(0 To 1, i)
Valeur(0, i) = Cells(NoLigne, 1)
Valeur(1, i) = 1
Do
With FL1.Range("A" & NoLigne + 1, [A65536].End(xlUp))
DerLig = 0
Set c = .Find(Valeur(0, i), LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
If c.Row > NoLigne Then
Valeur(1, i) = Valeur(1, i) + 1
DerLig = c.Row
Rows(c.Row).EntireRow.Hidden = True
End If
End If
Set c = Nothing
End With
Loop While DerLig > NoLigne
End If
NoLigne = NoLigne + 1
Loop While NoLigne < FL1.Range("A65536").End(xlUp).Row
'Affichage
For j = 1 To i
MsgBox "Qu de " & Valeur(0, j) & " = " & Valeur(1, j)
Next
FL1.Cells.EntireRow.Hidden = False
End Sub |
Partager