Dim toto As Range, nbcol As Integer nbcol = Range("B12:F20").Columns.Count Set toto = Range("B2:B20").SpecialCells(xlCellTypeVisible) ReDim tablo(1 To toto.Cells.Count, 1 To nbcol) Dim ou As Long ou = 0 Dim c As Range, i As Integer For Each c In toto ou = ou + 1 tablo(ou, 1) = c.Value For i = 2 To nbcol tablo(ou, i) = c.Offset(0, i - 1).Value Next Next Set toto = Nothing '=======preuve ======= pour affiche le contenu du tableau===== Dim j As Integer For i = 1 To UBound(tablo) For j = 1 To nbcol MsgBox tablo(i, j) Next Next
With Worksheets("Feuil2") .Range(.Cells(1, 1), .Cells(UBound(tablo), UBound(tablo, 2))).Value = tablo End With
If Cells(I, J).SpecialCells(xlCellTypeVisible) Then
tboall(l, k) = Cells(I, J)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questiontboall()=Range("B2:F20").SpecialCells(xlCellTypeVisible
Sub tableau() Dim tboall() As Variant Dim tbosub() As Variant Worksheets(1).Select 'tboall = Range("B2:F20") l = 0 For I = 2 To 20 l = l + 1 K = 0 For J = 2 To 6 If Cells(I, J).EntireRow.Hidden = False Then If J <> 3 Then K = K + 1 ReDim tboall(l, K) tboall(l, K) = Cells(I, J) End If End If Next J Next I ReDim Preserve tbosub(1) For I = 1 To UBound(tboall, 1) For J = 1 To UBound(tbosub) If tboall(I, 1) tbosub(J) Then blnExiste True Next J If blnExiste = False Then If tbosub(1) <> "" Then ReDim Preserve tbosub(UBound(tbosub) + 1) tbosub(UBound(tbosub)) = tboall(I, 1) End If blnExiste = False Next I For I = 1 To UBound(tbosub) Cells(I + 20, 7) = tbosub(I) Next I ReDim rezult(UBound(tbosub), 4) For J = 1 To UBound(tbosub) rezult(J, 2) = 0 rezult(J, 3) = 0 rezult(J, 4) = 0 For I = 1 To UBound(tboall, 1) If tboall(I, 1) = tbosub(J) Then rezult(J, 1) = tbosub(J) If tboall(I, 3) <> "" Or tboall(I, 4) <> "" Or tboall(I, 5) <> "" Then rezult(J, 2) = rezult(J, 2) + 1 End If If tboall(I, 3) "" And tboall(I, 4) "" And tboall(I, 5) = "" Then rezult(J, 3) = rezult(J, 3) + 1 End If rezult(J, 4) = rezult(J, 4) + 1 End If Next I Next J
If tboall(I, 3) <> "". A quel endroit faut-il redimensionner le tableau ?
'tboall = Range("B2:F20")
'tboall = Range("B2:F20")
tboall = Range("B2:F20").SpecialCells(xlCellTypeVisible)
l = 0 For I = 2 To 20 l = l + 1 K = 0 For J = 2 To 6 If Cells(I, J).EntireRow.Hidden = False Then If J <> 3 Then K = K + 1 ReDim tboall(l, K) tboall(l, K) = Cells(I, J) End If End If Next J Next I
Cells(21, 8) = tboall(1, 2) Cells(22, 8) = tboall(2, 2) Cells(23, 8) = tboall(3, 2) Cells(24, 8) = tboall(4, 2) Cells(25, 8) = tboall(5, 2)