[VBA] Sélectionner un tableau sans son en-tête

Voici une petite ligne de code astucieuse qui vous permet de sélectionner la plage entière d’un tableau sur la feuille Excel, hormis son en-tête, ce qui peut être pratique pour intégrer l’ensemble des valeurs du tableau dans une variable, par exemple.

Pour un tableau composé de plusieurs lignes et de plusieurs colonnes et dont l’en-tête commence dans la cellule A1 :

Range("A1").CurrentRegion.Offset(1).Resize(Range("A1").CurrentRegion.Rows.Count - 1, Range("A1").CurrentRegion.Columns.Count).Select

Explications

Nous connaissons maintenant bien Range("A1").CurrentRegion qui sélectionne toutes les cellules faisant partie de la plage de la cellule A1.
La commande Offset(1) représente un décalage vers le bas d’une ligne. Voir l’article suivant pour plus d’infos sur le fonctionnement de la commande Offset.
Nous savons aussi que la commande Resize redimensionne une cellule ou une plage de cellules et qu’elle est suivie du nombre de lignes et du nombre de colonnes permettant de redimensionner notre cellule de référence A1.
Tout logiquement, Range("A1").CurrentRegion.Rows.Count - 1 représente le nombre de lignes de notre plage.
Rows.Count repprésente le nombre de lignes de notre plage Range("A1").CurrentRegion; nous déduisons 1 car nous avons décalé notre plage de 1 vers le bas.

Il en va exactement de même pour le nombre de colonnes Range("A1").CurrentRegion.Columns.Count.
Une petite ligne qui peut s’avérer pratique, à adapter en fonction de vos besoins.

[VBA] Boostez la vitesse d’exécution de vos macros en utilisant les « variables Tableau » 2/3

Les variables à deux dimensions

Excel possède une fonctionnalité très astucieuse qui permet d’enregistrer simplement et rapidement une plage de données.
Supposons que nous souhaitions enregistrer la plage ci-dessous dans une variable que l’on nommera aa.

Plage VT

il nous suffit juste de taper la commande suivante : aa = Range("A1").CurrentRegion

Voilà, le tour est joué… pour vous en convaincre, nous allons recopier le contenu de la variable aa un peu plus loin dans la feuille à partir de la cellule J1.

Sub PlageVar()

Dim aa As Variant
aa = Range("A1").CurrentRegion
Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa

End Sub

Les 2 dimensions dont il est question correspondent aux lignes (1ere dimension) et aux colonnes (2eme dimension) de ce tableau enregistrées dans notre variable aa.
Nous avions vu, dans notre article précédent la commande Ubound().
Ajoutons la ligne suivante à notre macro :
MsgBox "Le nombre de lignes enregistré dans notre variable est de : " & UBound(aa, 1) & " et le nombre de colonnes est de : " & UBound(aa, 2)

Le sens de la commande Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) devient alors limpide : on redimensionne la cellule J1 pour avoir une plage de 20 lignes (UBound(aa, 1)) et 3 colonnes (UBound(aa, 2)).
Une fois la plage définie sur notre feuille Excel, un simple = aa nous permet de faire la copie.

Ajoutons également cette ligne juste après la précédente :
MsgBox "La cellule en 4eme ligne, 3eme colonne a la valeur " & aa(4, 3)

Cet exemple montre que, finalement, l’extraction des données d’une variable est très similaire à celui d’une cellule, il nous suffit de faire suivre le nom de notre valriable de son N° de ligne et de son N° de colonne, rien de plus simple.
Ci-dessous, le code complet :

Option Base 1
Sub PlageVar()

Sheets("PlageVar").Select

Dim aa As Variant
aa = Range("A1").CurrentRegion

Range("J1").Resize(UBound(aa, 1), UBound(aa, 2)) = aa

MsgBox "Le nombre de lignes enregistré dans notre variable est de : " & UBound(aa, 1) & " et le nombre de colonnes est de : " & UBound(aa, 2)
MsgBox "La cellule en 4eme ligne, 3eme colonne a la valeur " & aa(4, 3)

End Sub

Prenons le cas pratique suivant (le fichier Excel contenant tous les exemples de cet article se trouve à la fin de celui-ci) :

Employe mois

Supposons que nous souhaitions extraire tous les employés de la région Europe de ce tableau, voici le code :

Option Base 1
Sub Exp()

Dim aa As Variant, Tablo() As Variant
Dim i As Byte, j as Byte, c As Byte

Application.ScreenUpdating = False
Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve Tablo(14, c)
        Tablo(1, c) = aa(i, 1)
        Tablo(2, c) = aa(i, 2)
        Tablo(3, c) = aa(i, 3)
        Tablo(4, c) = aa(i, 4)
        Tablo(5, c) = aa(i, 5)
        Tablo(6, c) = aa(i, 6)
        Tablo(7, c) = aa(i, 7)
        Tablo(8, c) = aa(i, 8)
        Tablo(9, c) = aa(i, 9)
        Tablo(10, c) = aa(i, 10)
        Tablo(11, c) = aa(i, 11)
        Tablo(12, c) = aa(i, 12)
        Tablo(13, c) = aa(i, 13)
        Tablo(14, c) = aa(i, 14)
    End If
Next i
    Range("A20").Resize(UBound(Tablo, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo)

End Sub

Première étape : Nous enregistrons le tableau dans notre variable aa aa = Range("A1").CurrentRegion.
Ensuite, la boucle vérifie, pour chaque employé, la zone à laquelle il appartient For i = LBound(aa, 1) To UBound(aa, 1).
La commande Ubound() nous est maintenant familière, il s’agit du nombre total d’entrées, ici, dans notre première dimension.
La commande Lbound() est nouvelle; elle identifie le N° de la première entrée d’une variable… utile lorsqu’on ne veut pas se préoccuper de savoir si la première entrée a un index de 0 ou de 1… ici, on l’occurence, le problème ne se pose pas parce que nous avons spécifié Option Base 1 en début de code.

Nous définissons ensuite la condition : If aa(i, 2) = "Europe" Then exactement comme nous le ferions pour analyser le contenu de la cellule; nous l’avons vu précédemment il nous suffit simplement de changer la commande Cells et la remplacer par le nom de notre variable pour que la macro travaille au niveau de la mémoire plutôt que des cellules.

Si la condition est vérifiée, nous alimentons une variable tablo qui va enregistrer toutes les informations relatives aux lignes dont la colonne C = »Europe ». Puisque nous ne connaissons pas à l’avance le nombre d’employés de la zone Europe, nous allons bâtir notre tablo au fur et à mesure que le programme rencontrera la valeur Europe; nous allons donc utiliser la commande Redim Preserve, pour redimensionner notre variable tablo.

Important
Dans une variable à 2 dimensions, nous ne pouvons redimensionner qu’une seule des deux dimensions. C’est toujours celle qui est déclarée en 2eme position !
Jusqu’ici, la première dimension de notre variable tablo correspondait au nombre de lignes de notre tableau et la seconde, au nombre de colonnes.

Dans notre cas de figure, le nombre de colonnes restera toujours le même, à savoir 14.
Le nombre de lignes est, lui, inconnu et peut évoluer. La variable c sera incrémentée au fur et à mesure que la condition sera vérifiée et nous servira à redimensionner notre tablo c = c + 1. La commande Redim Preserve se rédige donc ainsi : ReDim Preserve Tablo(14, c)

Nous devons maintenant enregistrer chaque valeur des lignes correspondant au critère « Europe » dans la variable tablo.
C’est ce que font les 14 lignes de code suivantes : Tablo(1, c) = aa(i, 1)
La première valeur de la « ligne » c de notre variable tablo = la 1ere valeur de la « ligne » i de la variable aa (qui correspond à la cellule (i, 1)). et ainsi de suite.

Une fois la boucle terminée, nous allons donc inscrire le résultat de notre recherche.
Nous avons vu, au début de cet article qu’il suffisait de redimensionner une plage à partir d’une cellule et d’y affecter le contenu de notre variable. c’est exactement ce que fait la commande suivante : Range("A20").Resize(UBound(Tablo, 2), UBound(Tablo, 1)) = Application.Transpose(Tablo).

Elle redimmensionne la cellule A20 du nombre total de lignes Resize(UBound(Tablo, 2)(qui est maintenant la 2eme dimension, puisque nous avons inversé le sens de nos dimensions à cause de la contrainte imposée par notre variable tablo de ne pouvoir redimensionner qu’une seule des deux dimensions) et du nombre total de colonnes UBound(Tablo, 1) contenus dans notre tablo.

Puisque nous avons inversé le sens lignes/colonnes lors de l’enregistrement, il va nous falloir récupérer le sens originel de nos dimensions; c’est ce que se charge de faire la commande Application.Transpose(tablo).
Mais nous reviendrons plus en détail sur la copie du contenu des variables sur la feuille Excel dans le 3eme et dernier article dédié aux variables Tableau.

Ce petit exemple nous a permis de mieux comprendre le fonctionnement des variables Tableau, cependant on peut améliorer et simplifier son code.

La première modification va porter sur cette ligne de code : ReDim Preserve Tablo(14, c)
Le nombre de colonnes, 14, est, ici, fixe… mais peut-être qu’à l’avenir, ce document sera amené à évoluer et le nombre de colonnes pourrait changer, donc autant mettre cette valeur sous forme de variable, ainsi, nous n’aurons pas à modifier la macro en cas de changement du tableau. Nous savons maintenant définir le nombre de colonnes de notre variable aa en utilisant la commande Ubound(aa,2); il nous suffit juste de remplacer 14 par cette commande : ReDim Preserve tablo(UBound(aa, 2), c).

L’autre changement consiste à améliorer l’enregistrement de toutes les valeurs d’une ligne dans notre tablo.
Nous avons écrit 14 lignes de commande pour enregistrer les valeurs des 14 colonnes du tableau.
Nous allons donc modifier le code pour contourner cette manipulation assez fastidieuse et introduire une boucle qui enregistrera automatiquement toutes les données relatives à une même ligne dans le tablo.

For j = LBound(aa, 2) To UBound(aa, 2)
    tablo(j, c) = aa(i, j)
Next j

For j = LBound(aa, 2) To UBound(aa, 2) nous dit de boucler entre les N° de la première et la dernière colonne contenues dans la variable aa.

Puis nous remplaçons les N° de colonne par la variable j : tablo(j, c) = aa(i, j), qui, tour à tour, prend le N° de chacune des 14 colonnes de notre exemple et remplace les 14 lignes de code.

Notre nouveau code ressemble à ça :

Option Base 1
Sub Exp()

Dim aa As Variant, tablo() As Variant
Dim i As Byte, j As Byte, c As Byte

Application.ScreenUpdating = False
Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve tablo(UBound(aa, 2), c)
        For j = LBound(aa, 2) To UBound(aa, 2)
            tablo(j, c) = aa(i, j)
        Next j

    End If
Next i
    Range("A20").Resize(UBound(tablo, 2), UBound(tablo, 1)) = Application.Transpose(tablo)

End Sub

Rédigé ainsi, le code est plus digeste à lire mais il persiste un inconvénient : pour chaque ligne enregistrée, la commande For i = LBound(aa, 1) To UBound(aa, 1) boucle 14 fois, ce qui, dans l’absolu, peut prendre énormément de temps si le nombre de lignes et de colonnes devient important.

Nous allons donc faire en sorte qu’à chaque fois qu’une ligne doit être enregistrée dans le tablo, elle le soit dans son intégralité.

C’est la commande Index que nous avons déjà étudiée dans nos articles précédents qui va nous permettre de réaliser cette manipulation. Pour rappel, l’index est le N° d’un objet dans une collection d’objets de même nature. Ici, il va s’agir de déterminer une ligne de la variable aa parmi toutes les autres lignes de cette même variable et l’enregistrer dans le tablo.

Nous avons vu, au début de cet article, qu’une variable pouvait contenir une infinité d’informations lorsque nous avons écrit la commande aa = Range("A1").CurrentRegion.
Nous allons procéder exactement de la même façon pour l’enregistrement d’une ligne de aa dans tablo : nous allons donc redéfinir notre variable tablo comme n’ayant qu’une seule dimension et non plus 2, il nous suffit, pour cela de modifier la commande Redim Preserve comme ceci : ReDim Preserve tablo(c).
Nous n’avons plus qu’à enregistrer la ligne entière avec ce code : tablo(c) = Application.Index(aa, i) qui signifie concrêtement : dans la « case » c de la variable tablo, enregistrer la « ligne entière » i de la variable aa.

Il nous reste à modifier la ligne qui permet d’inscrire les données du tablo dans la feuille Excel :
Range("A20").Resize(UBound(tablo), UBound(aa, 2)) = Application.Transpose(Application.Transpose(tablo))

A noter qu’on peut identifier une valeur parmi les 14 de chaque ligne; toujours en utilisant la fonction Index :
Supposons que l’on veuille récupérer la 6eme valeur de la 5eme ligne de notre tablo : MsgBox Application.Index(tablo(5), 6)

Et voici le code complet pour notre dernier exemple :

Option Base 1
Sub Exp()
Dim aa As Variant, tablo() As Variant
Dim i As Byte, j As Byte, c As Byte

Application.ScreenUpdating = False

Sheets("Exp").Select
Range("A20").CurrentRegion.ClearContents

aa = Range("A1").CurrentRegion

For i = LBound(aa, 1) To UBound(aa, 1)
    If aa(i, 2) = "Europe" Then
        c = c + 1
        ReDim Preserve tablo(c) 'cas N° 3
        tablo(c) = Application.Index(aa, i)
    End If
Next i

Range("A20").Resize(UBound(tablo), UBound(aa, 2)) = Application.Transpose(Application.Transpose(tablo))
MsgBox Application.Index(tablo(5), 6)

End Sub

Nous avons fait le tour de ce qu’il faut savoir sur les variables Tableau, dans le dernier volet de cette série d’articles, nous examinerons plus en détail les outils à notre disposition pour retranscrire les valeurs d’une variable sur la feuille Excel.

Ci-dessous le fichier au format .xls contenant les exemples de cet article.
Variables tablo 2

[VBA] Déterminer la dernière ligne/colonne d’un tableau

Identifier la dernière cellule non vide d’une colonne

En VBA, une fonction récurrente consiste à déterminer la dernière ligne d’un tableau.
Prenons l’exemple ci-dessous :

Tableau

Pour celles et ceux qui n’ont besoin que des lignes de code, les voici avec leurs descriptions respectives en remarque

'déclaration des variables
Dim derniereLigne As Long
Dim derniereColonne As Integer
Dim myRange As Range
Range("A" & Rows.Count).End(xlUp).Select 'sélection de la dernière cellule non vide de la colonne A
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row 'n° de la dernière ligne non vide de la colonne A
Range("A" & Rows.Count).End(xlUp).Offset(1).Select 'sélection de la première cellule vide de la colonne A
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row + 1 'n° de la première ligne vide de la colonne A
Set myRange = Range("A" & Rows.Count).End(xlUp) 'enregistrement de la dernière cellule non vide de la colonne A dans la variable myRange
myRange.Select 'sélection de la variable myRange
Cells(1, Columns.Count).End(xlToLeft).Select 'sélection de la dernière cellule non vide de la ligne 1
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column 'n° de la dernière colonne non vide de la ligne 1
Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Select 'sélection de la première cellule vide de la ligne 1
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column + 1 'n° de la première colonne vide de la ligne 1

Explications

Une ligne de code couramment utilisée est la suivante :

Range("A" & Rows.Count).End(xlUp).Select

Cette ligne signifie : sélectionne (.Select) la dernière cellule non vide (.End) vers le haut (xlUp) à partir de la dernière cellule (&Rows.Count) de la colonne A (Range(« A »))
Donc cette ligne de commande se comporte exactement comme si vous vous placiez dans la dernière cellule de la colonne A (cellule A1048576 pour la version 2007 d’Excel) et que vous appuyiez sur CTRL + flêche du haut.

Pourquoi aller du bas de la feuille vers le haut et pas l’inverse ? Tout simplement, pour éviter une erreur de sélection s’il s’avérait que le tableau contient des cellules vides.

Essayez donc le code suivant en effaçant la cellule A3 par exemple : Range("A1").End(xlDown).Select

Ce qu’il faut bien comprendre ici est la signification de Rows.Count
Rows.Count signifie littéralement : dernière ligne.
En l’occurence,il signifie ici, dernière ligne de mon onglet soit la ligne 1048576 comme nous venons de le voir mais il aurait pu aussi s’agir de la dernière ligne d’une plage bien précise (dans cet exemple :
derniereLigne = Range("A1").CurrentRegion.Rows.Count la variable derniereLigne à une valeur égale à 4)

Une variante consiste à récupérer le N° de la dernière ligne non vide d’une colonne dans une variable plutôt que de sélectionner cette dernière ligne :

 Dim derniereLigne As Long
 derniereLigne = Range("A" & Rows.Count).End(xlUp).Row'passez le curseur sur dernièreLigne pour en lire la valeur ou utilisez la commande MsgBox ci-dessous
 MsgBox derniereLigne 

Ici, la variable derniereLigne récupère la valeur 4.
Nous avons, pour cela, légèrement modifié, le code précédent : nous avons remplacé la commande Select par la commande Row.
Ne confondons pas Rows en VBA qui représente une ligne dans Excel avec la commande Row qui représente un N° de ligne.

Pour sélectionner la première cellule vide d’une colonne :

Range("A" & Rows.Count).End(xlUp).offset(1).Select

Pour info :
Offset(1) signifie : décaler de 1 vers le bas
Offset(,2) signifie : décaler de 2 vers la droite
Offset(-3) signifie : décaler de 3 vers le haut
Offset(,-2) signifie : décaler de 2 vers la gauche
Offset(-5,6) signifie : décaler de 5 vers le haut et de 6 vers la droite

Pour obtenir le N° de la première ligne vide d’une colonne :

Dim derniereLigne As Long
derniereLigne = Range("A" & Rows.Count).End(xlUp).Row + 1

Enfin, il est utile de savoir enregistrer l’emplacement de la dernière cellule non vide d’une colonne dans une variable :

Dim myRange As Range
Set myRange = Range("A" & Rows.Count).End(xlUp)
myRange.Select

Ici, l’enregistrement du résultat dans la variable myRange est précédé de la commande Set puisqu’on attribue un « objet » à une variable (ici, une cellule) et non pas une valeur alphanumérique comme on l’a fait dans lors de l’utilisation de la variable derniereLigne.

Identifier la dernière cellule non vide d’une ligne

Sélection de la dernière colonne non vide d’une ligne :

Cells(1, Columns.Count).End(xlToLeft).Select

Ici, nous avons remplacé la syntaxe d’identification de la cellule; Range(« A »&Rows.count) devient Cells(1,Columns.Count) ou 1 est le N° de ligne de la cellule et Columns.Count, le N° de la colonne. Nous avons donc sélectionné la dernière cellule de la ligne 1 et nous avons déplacé le curseur vers la gauche.

N° de la dernière colonne non vide d’une ligne :

Dim derniereColonne As integer
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column

Sélection de la première colonne vide d’une ligne :

Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Select

N° de la dernière colonne vide d’une ligne :

Dim derniereColonne As Integer
derniereColonne = Cells(1, Columns.Count).End(xlToLeft).Column + 1

 

Et en réponse au commentaire de Clamar75 qui souhaite déterminer la dernière ligne d’un tableau dont les colonnes auraient des tailles différentes

Range("A" & Rows.Count).End(xlUp).End(xlUp).Offset(Range("A" & Rows.Count).End(xlUp).End(xlUp).CurrentRegion.Rows.Count - 1).Select

Voilà, cette méthode n’a plus de secret pour vous; il en existe d’autres pour identifier la dernière ligne ou la dernière colonne d’un onglet… que nous verrons dans un prochain billet à l’aide de la méthode Find.