Discussion :

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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,

[Patrice740]

Bonjour le Fil

Je viens de découvrir cet excellent post grâce à un référencement récent sur un autre forum.

Je tiens à préciser qu'il faut être prudent en utilisant UsedRange : la plage UsedRange ne commence pas toujours en A1.

De ce fait, l'exemple 6 ne réalise pas exactement la même chose que l'exemple 5, pour cela, il faut écrire :

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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
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
Dim PreLig As Long, PreCol As Integer
 
    Set FL1 = Worksheets("Feuil2")
 
    'Détermine la première ligne renseignée de la feuille de calculs
    PreLig = FL1.UsedRange.Row
 
    'Détermine la première colonne renseignée de la feuille de calculs
    PreCol = FL1.UsedRange.Column
 
    '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 = PreLig To DerLig
        For NoCol = PreCol To DerCol
            Var = FL1.Cells(NoLig, NoCol)
        Next
    Next
 
End Sub

[Pierre Fauconnier]

Salut.

Perso, je ne vois pas bien à quoi servent ces 6 "méthodes"? Normalement, parcourir les cellules d'une plage et déterminer une plage sont deux choses différentes.

Parcourir les cellules d'une plage, c'est une double boucle, ici sur la UsedRange d'une feuille, mais que l'on pourrait généraliser en passant la plage en paramètres. Ca fonctionne quelle que soit la plage sans qu'elle doive forcément commencer en A1, et ça fonctionne pour une plage monocellule, monoligne, monocolonne, ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub Test()
  Dim rng As Range, r As Long, c As Long
 
  Set rng = Feuil1.UsedRange
  For r = 1 To rng.Rows.Count
    For c = 1 To rng.Columns.Count
      Debug.Print rng(r, c)
    Next
  Next
End Sub

Je ne vois pas l'utilité de split, de derlig, dercol etc... Surtout que sur une plage monocellule (cas d'école, ok), l'adresse n'est composée que de l'adresse de la cellule, par exemple $C$4 et par $C$4:$C$4 et donc le code avec Split plante dans ce cas précis.

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

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

Cordialement,

Le Split ne parcourt pas les cellules! Il permet de retrouver une partie de l'adresse de la plage. Ce n'est pas du tout la même chose!



Et si on souhaite l'adresse de la dernière cellule d'une plage, il y a plus fiable et générique que le split qui plante en monocellule...
Adresse de la première cellule d'une plage: rng(1).Address
Adresse de la dernière cellule d'une plage: rng(rng.Rows.Count, rng.Columns.Count).Address

[Philippe Tulliez]

Bonjour Patrice,
Curieusement, je n'ai jamais eu besoin en production d'utiliser cette propriété qui est d'abord trompeuse parce-qu'elle peut renvoyer un adressage faussé comme d'ailleurs l'utilisation manuelle du Ctrl + End après avoir fait un "Delete" du contenu d'une cellule.
Du fait de la bonne organisation de mes données, j'ai toujours utilisé la propriété CurrentRegion que j'ai abandonné depuis l'usage exclusif des tableaux structurés

[Patrice740]

Bonjour Philippe, Bonjour Pierre,

Bonjour Patrice,
Du fait de la bonne organisation de mes données, j'ai toujours utilisé la propriété CurrentRegion que j'ai abandonné depuis l'usage exclusif des tableaux structurés

Malheureusement les données sont parfois très mal organisées avec des lignes vides et des colonnes vides !
Dans ce cas, pour les réorganiser, j'utilise Find pour déterminer la dernière ligne et la dernière colonne. J'évite UsedRange vu son manque de fiabilité.

L'objet de mon post précédent est uniquement de présenter une correction de l'exemple 6 pour qu'il respecte l'affirmation qu'il contient : réalise la même opération que le 5 (mais pas de cautionner l'emploi de UsedRange ou de Split).

[OtObOx]

Bonjour,

comme Pierre Fauconnier, je ne comprends pas non plus pourquoi déployer toute cette énergie pour parcourir un range donné.

Ce que je fais pour parcourir une colonne d'un range :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Sub ParcourirColonne(rg As Range, noCol As Integer)
    Dim c As Range
 
    For Each c In rg.Columns(noCol).Cells
        Debug.Print c.Value
    Next
End Sub

Utilisation

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
'Retourne les valeurs de la colonne "B10:B20" du tableau "B10:D20"
ParcourirColonne range("B10:D20"), 1

Même principe pour parcourir une ligne mais en remplaçant Columns(noCol) par Rows(noLigne)