[VBA] Itérer rapidement sur un tableau filtré avec Range.Areas

Bonjour à tous,

Je viens de découvrir une petite astuce : pour récupérer les cellules (visibles) d'une plage filtrée, on peut tirer avantage de la propriété Range.Areas property (Excel) | Microsoft Learn.

Cela permet de récupérer directement les groupes de cellules contiguës dans une Range, et donc on peut itérer sur cette liste (.Areas) plutôt que sur les cellules (.Cells) de la range pour aller plus vite.

Par exemple, avec un tableau en A1:A11 (filtré sur la valeur "2") tel que :

Le code suivant

Sub Ex()
  Dim tbl As ListObject
  Set tbl = ActiveSheet.ListObjects(1)

  Dim cellulesVisibles As Range
  Set cellulesVisibles = tbl.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

  Dim blockIter As Range
  Debug.Print "AREAS"
  For Each blockIter In cellulesVisibles.Areas
    Debug.Print blockIter.Address
  Next blockIter
  Debug.Print "RANGES"
  For Each blockIter In cellulesVisibles
    Debug.Print blockIter.Address
  Next blockIter
End Sub

Donne l'output

AREAS

$A$2

$A$6:$A$8

$A$10

RANGES

$A$2

$A$6

$A$7

$A$8

$A$10

Soit 3 itérations pour la boucle sur les Aires, contre 5 pour la boucle sur les cellules.

Maintenant, si l'on fait par exemple une opération d'extraction de ces cellules dans une autre feuille, on peut gagner beaucoup de temps si notre méthode d'extraction est "simple" (écriture directe de type .Copy). Par exemple :

Sub Ex2()
  Dim tbl As ListObject
  Set tbl = ActiveSheet.ListObjects(1)

  Dim cellulesVisibles As Range
  Set cellulesVisibles = tbl.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

  Dim sht2 As Worksheet: Set sht2 = ThisWorkbook.Worksheets(2)

  Dim blockIter As Range
  For Each blockIter In cellulesVisibles.Areas
    blockIter.Copy sht2.Cells(sht2.Rows.Count, 1).End(xlUp).Offset(1)
  Next blockIter
End Sub

On ne va faire qu'une copie par "bloc" = Area au lieu d'une copie par cellule. Sur de grands tableaux, on peut observer un vrai gain de temps !

top !

Pas mal et inttéressanr !

Par contre il me semble que l'on peut raccourcir le code ex2

Sub Ex2()
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)

Dim cellulesVisibles As Range
Set cellulesVisibles = tbl.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

Dim sht2 As Worksheet
Set sht2 = ThisWorkbook.Worksheets(2)
cellulesVisibles.Copy sht2.Cells(sht2.Rows.Count, 1).End(xlUp).Offset(1)
End Sub

On évite la boucle plus bas

Crdlt

Bonjour à tous,

Suite à la remarque de @Dan que je salue (et @h2so4 bien entendu), je me suis rendu compte que l'exemple 2 était très mal choisi (trop simpliste) pour illustrer l'intérêt du Range.Area.

Ci-après un nouvel exemple qui montre comment peupler un tableau en mémoire uniquement avec les cellules filtrées, l'objectif de passer par ce tableau en mémoire c'est bien sûr pour effectuer diverses opérations sur les valeurs avant de les renvoyer dans la feuille. Car là, on gagne beaucoup de temps par rapport à faire un appel à Range.Value sur chaque cellule.

Dans le fichier ci-joint la macro Ex3 qui boucle sur les Areas et la macro Ex4 qui boucle sur les Cells pour comparer (12k lignes dont 4400 filtrées). Personnellement (ça dépend de votre PC) j'observe 0,00586 s pour le traitement des Areas contre 0,0176 s pour celui cell by cell. Donc un temps de traitement divisé par 3, c'est pas mal.

Pour info, si certains se demandent comment boucler avec les indices et les Areas, j'ai procédé comme ceci :

  ' stockage en memoire du tableau des cellules visibles uniquement
  Dim vbTableauFinal() As Variant
  '   calcul du nombre de lignes nécessaires
  Dim nbLignes As Long
  nbLignes = Evaluate("=COUNTIF(" & tbl.ListColumns(1).DataBodyRange.Address(External:=True) & ",1)")
  '   redim tableau memoire
  ReDim vbTableauFinal(1 To nbLignes, 1 To tbl.ListColumns.Count)

  '   remplissage
  Dim blockIter As Range      ' par exemple sera la plage A3:C6
  Dim blockVals As Variant    ' les valeurs du block
  Dim vbLigneActive As Long   ' la ligne a remplir dans le tableau en memoire
  Dim i As Long, j As Long    ' i: ligne dans le bloc courant, j: colonne

  For Each blockIter In cellulesVisibles.Areas
    blockVals = blockIter.Value
    For i = LBound(blockVals, 1) To UBound(blockVals, 1)
      vbLigneActive = vbLigneActive + 1
      For j = LBound(blockVals, 2) To UBound(blockVals, 2)
        vbTableauFinal(vbLigneActive, j) = blockVals(i, j)
      Next j
    Next i
  Next blockIter