Boucles pour parcourir une colonne, une ligne, une plage de données - 2 méthodes

[ouskel'n'or]

Question récurrente s'il en est : "Comment parcourir une colonne, une ligne, une plage de cellules ?"
Réponse certainement données dans la FAQ, je viens cependant proposer deux méthodes, celle utilisant la/les boucles For ... Next et celle utilisant la boucle For each ... Next

Exemple 1
Parcours simple des cellules d'une colonne donnée, ici la colonne 1, à l'aide d'une boucle For ... Next sur le numéro de ligne de la colonne.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
Sub For_X_to_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer
Dim NoLig As Long, DerLig As Long, Var As Variant
 
    'Instance de la feuille qui permet d'utiliser FL1 partout dans
    'le code à la place du nom de la feuille
    Set FL1 = Worksheets("Feuil2")
 
    'Détermine la dernière ligne renseignée de la feuille de calculs
    '(Voir explication sur l'utilisation de Split en bas de cette discussion)
    DerLig = Split(FL1.UsedRange.Address, "$")(4)
 
    'Fixe le N° de la colonne à lire
    NoCol = 1
 
    'Utilisation du N° de ligne dans une boucle For ... Next
    For NoLig = 1 To DerLig
        Var = FL1.Cells(NoLig, NoCol)
 
        'Pour tester : Affiche les variables dans la fenêtre Exécution de VBA
        Debug.Print Var
    Next
    Set FL1 = NoThing
End Sub

La même procédure est utilisable pour lire les données d'une colonne ligne par ligne, ou pour lire les donnée d'une ligne colonne par colonne

Exemple 1 (bis) (code seul simplifié)
Parcours simple des cellules d'une Colonne déterminée, ligne après ligne

Code :

1
2
3
4
5
6
7
8
9
10
Sub For_X_to_Next_Ligne()
Dim FL1 As Worksheet, NoCol As Integer
Dim NoLig As Long, Var As Variant
    Set FL1 = Worksheets("Feuil2")
    NoCol = 1 'lecture de la colonne 1
    For NoLig = 1 To Split(FL1.UsedRange.Address, "$")(4)
        Var = FL1.Cells(NoLig, NoCol)
    Next
    Set FL1 = NoThing
End Sub

Exemple 2
Parcours simple des cellules d'une Ligne déterminée, colonne après colonne

Code :

1
2
3
4
5
6
7
8
9
10
11
Sub For_X_to_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer
Dim NoLig As Long, Var As Variant
    Set FL1 = Worksheets("Feuil2")
    NoLig = 5 'Lecture de la ligne 5
    '(Voir explication sur l'utilisation de Split en bas de cette discussion)
    For NoCol = 1 To Columns(Split(FL1.UsedRange.Address, "$")(3)).Column
        Var = FL1.Cells(NoLig, NoCol)
    Next
    Set FL1 = NoThing
End Sub

Exemple 3
Seconde méthode utilisable : La foucle For each ... Next
L'exemple ci-dessous lit chaque cellule d'une seule colonne
NoCol1 = 1 ; NoCol2 = 1

On constate immédiatement que le code suivant est plus lourd que les précédents (Exemple 1 et 2)
S'il est donné malgré tout c'est afin de conduire aux exemples suivants en abordant la notion de plage de données

Code :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
Sub For_Each_Next_Colonne()
Dim FL1 As Worksheet, Cell As Range, NoCol1 As Integer, NoCol2 As Long
Dim DerLig As Long, Plage As Range
'Les données récupérées
Dim Var1, Var2, Var3, adres As String, NoLig As Long, NoCol As Integer
    'Instance de la feuille : Permet d'utiliser FL1 partout dans ...
    '... le code à la place de Worksheets("Feuil2")
    Set FL1 = Worksheets("Feuil2")
 
    'Fixe le N° de première colonne de la plage à lire
    NoCol1 = 1
 
    'Fixe le N° de la dernière colonne de la plage à lire
    NoCol2 = 1
 
    'Détermine la dernière ligne renseignée de la feuille de calculs
    DerLig = Split(FL1.UsedRange.Address, "$")(4)
 
    'où FL1.Range(FL1.Cells(1, NoCol1), FL1.Cells(Derlig, NoCol2)) détermine
    'la plage de cellules à lire
 
    With FL1
        Set Plage = .Range(FL1.Cells(1, NoCol1), FL1.Cells(DerLig, NoCol2))
        'Utilisation de l'objet range (Cell) dans une boucle For Each... Next
        For Each Cell In Plage
 
            '*** Récupération des valeurs de plusieurs cellule ***
            'Valeur de la cellule lue
            Var1 = Cell.Value
            'Valeur de la cellule de la même ligne, colonne NoCol + 1
            Var2 = Cell.Offset(0, 1)
            'Valeur de la cellule de la même ligne, colonne NoCol + 2
            Var3 = Cell.Offset(0, 2)
 
            '*** Récupération de l'adresse de la cellule lue ***
            'Adresse complète
            adres = Cell.Address
            'Numéro de ligne
            NoLig = Cell.Row
            'Numéro de colonne
            NoCol = Cell.Column
 
            'Pour tester : Affiche les variables dans la fenêtre Exécution de VBA
            Debug.Print adres & " " & NoLig & " " & NoCol & " "
            Debug.Print Var1 & " " & Var2 & " " & Var3
        Next
    End With
    Set FL1 = NoThing
    Set Plage = NoThing
End Sub

Exemple 4
La même méthode permet de parcourir chaque cellule d'une plage de données.

L'exemple ci-après lit les cellules d'une plage de données prè-déterminée
Exemple :
Adresse de la plage : "B3:E15"

Les données seront lues colonne après colonne pour une ligne, puis ligne après ligne :
Ligne1, Colonne1 ; Ligne1, Colonne2 ; Ligne1, Colonne3 ; ... puis
Ligne2, Colonne1 ; Ligne2, Colonne2 ; Ligne2, Colonne3 ; ...
etc.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sub For_Each_Next_Plage()
Dim FL1 As Worksheet, Cell As Range, Plage As Range
Dim Var1
 
    Set FL1 = Worksheets("Feuil2")
    With FL1
        'Détermination de la plage de cellules à lire
        'Peut s'écrire en utilisant l'objet range de la plage
        'For Each Cell In .Range("B3:E15")
 
        'ou en utilisant l'objet Plage (range) de la plage
        Set Plage = .Range("B3:E15")
        For Each Cell In Plage
 
            'Valeur de la cellule lue
            Var1 = Cell.Value
        Next
    End With
    Set FL1 = NoThing
    Set Plage = NoThing
End Sub

Exemple 5
L'exemple suivant, utilise la propriété UsedRange pour lire toutes les cellules de la plage de données d'une feuille de calculs
Ici aussi, les données sont lues colonne après colonne pour une ligne, puis ligne après ligne :

Code :

1
2
3
4
5
6
7
8
9
10
11
Sub For_Each_Next_UsedRange()
Dim FL1 As Worksheet, Cell As Range
    Set FL1 = Worksheets("Feuil2")
    With FL1
        'Utilisation de la propriété UsedRange dans une boucle For Each... Next
        For Each Cell In .UsedRange
            Var1 = Cell.Value
        Next
    End With
    Set FL1 = NoThing
End Sub

Exemple 6
Cet exemple réalise la même opération en utilisant deux boucles imbriquées
pour une lecture colonne après colonne pour une ligne, puis ligne après ligne.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Sub For_Next_Plage()
Dim FL1 As Worksheet, Cell As Range, NoCol As Integer, NoLig As Long
Dim DerLig As Long, DerCol As Integer, Var As Variant
 
    Set FL1 = Worksheets("Feuil2")
 
    'Détermine la dernière ligne renseignée de la feuille de calculs
    DerLig = Split(FL1.UsedRange.Address, "$")(4)
 
    'Détermine la dernière colonne renseignée de la feuille de calculs
    DerCol = Columns(Split(FL1.UsedRange.Address, "$")(3)).Column
 
    For NoLig = 1 To DerLig
        For NoCol = 1 To DerCol
            Var = FL1.Cells(NoLig, NoCol)
        Next
    Next
 
End Sub

Split
Une explication sur l'utilisation de Split(FL1.UsedRange.Address, "$")(4) :

Worksheets("Feuil1").UsedRange.address donne l'adresse de la plage de données sous la forme "A1:" & DernièreColonne & dernièreLigne"
Par exemple "$A$1:$H$75"
En réalité on utilisera Split(Worksheets("Feuil1").UsedRange.address, "$") ou Split(FL1.UsedRange.address, "$")

En utilisant "$" comme séparateur de données, Split("$A$1:$H$75", "$") crée un tableau de taille 5 (0 à 4) de cette adresse
Exemple :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
Sub Ex1()
Dim adres As Variant, DerCol As Variant, DerLig As Long
    adres = Split("$A$1:$H$75", "$")
    adres(0) = ""
    adres(1) = "A"
    adres(2) = "1"
    adres(3) = "H"
    adres(4) = "75"
    'donne
    MsgBox Split("$A$1:$H$75", "$")(4) 'Dernière ligne = 75
    MsgBox Columns(Split("$A$1:$H$75", "$")(3)).Column 'Dernière colonne (H) que Columns convertit en numéro (8)
End Sub

De même, en utilisant ":" comme séparateur, on obtient un tableau de taille 2 (0 à 1) contenant les adresses de début et de fin de plage
Exemple :

Code :

1
2
3
4
5
6
7
Sub Ex2()
Dim adres As Variant, Ad1 As String, Ad2 As String
    adres = Split("$A$12:$F$75", ":")
    'donne
    MsgBox Split("$A$12:$F$75", ":")(0)   '= $A$12
    MsgBox Split("$A$12:$F$75", ":")(1)   '= $F$75
End Sub

(A compléter)

[hunteshiva]

Bonjour,

super le code que tu a laissé, il m'aide beaucoup

par contre une partie que je ne comprend pas,

Code :

1
2
'Dernière colonne (H) que Columns convertit en numéro (8)
MsgBox Columns(Split("$A$1:$H$75", "$")(3)).Column

la fonction "Columns" n’existe pas chez moi

[casefayere]

Bonjour à tou(te)s, ouskel'n'or,

Une façon inédite (pour moi) de parcourir les cellules avec Split, Bravo !

Cordialement,