Trouver la dernière colonne avec des données dans une feuille Excel, Excel VBA

Trouver la dernière colonne avec des données dans une feuille Excel, Excel VBA

Voici une explication détaillée pour trouver la dernière colonne avec des données dans une feuille Excel à l’aide de VBA.
Objectif :
L’objectif est d’écrire un code VBA qui permet de trouver dynamiquement la dernière colonne contenant des données dans une feuille de calcul Excel. Cette tâche est courante lors de l’automatisation de traitements de données dans Excel à l’aide de VBA, surtout quand le nombre de colonnes varie.
Étapes et Code :
1. Comprendre la disposition des données dans Excel : Dans Excel, les données peuvent être réparties sur plusieurs lignes et colonnes. Tandis que la dernière ligne peut être trouvée en utilisant des méthodes comme Cells(Rows.Count, 1).End(xlUp).Row, trouver la dernière colonne nécessite une approche différente, car Excel ne propose pas de fonction native pour cela.
2. Stratégie : Nous allons utiliser la méthode End(xlToLeft), qui permet de partir d’une cellule donnée (généralement la cellule la plus à droite de la ligne) et de trouver la dernière colonne contenant des données. Cela fonctionne de la même manière que End(xlUp) pour les lignes.
Nous allons utiliser cette méthode sur la première ligne, qui est généralement la ligne des en-têtes, pour déterminer la dernière colonne. Si la première ligne est vide, nous pouvons aussi vérifier l’ensemble de la feuille ou la plage utilisée.
3. Code VBA : Voici le code VBA qui trouvera la dernière colonne contenant des données :

Sub TrouverDerniereColonne()
    Dim derniereColonne As Long
    Dim ws As Worksheet
    ' Définir la feuille de travail active
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Trouver la dernière colonne dans la première ligne avec des données
    derniereColonne = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    ' Afficher le résultat dans une boîte de message
    MsgBox "La dernière colonne avec des données est : " & derniereColonne
End Sub

Explication du code :
1. Déclaration des variables :

• Dim derniereColonne As Long : Cette variable contient le numéro de la dernière colonne avec des données. Le type Long est utilisé car Excel supporte plus de 32 000 colonnes (jusqu’à la colonne XFD).
• Dim ws As Worksheet : Cette variable est utilisée pour stocker l’objet de la feuille de calcul.

2. Définir la feuille de travail :

• Set ws = ThisWorkbook.Sheets("Sheet1") : Cette ligne définit la feuille sur laquelle vous travaillez. Remplacez « Sheet1 » par le nom de votre feuille. ThisWorkbook fait référence au classeur dans lequel la macro s’exécute.

3. Trouver la dernière colonne :

• • ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column : Cette ligne effectue l’opération principale pour trouver la dernière colonne.
    • ws.Cells(1, ws.Columns.Count) : Cela sélectionne la dernière cellule de la première ligne (i.e. la cellule de la première ligne et de la dernière colonne possible). ws.Columns.Count retourne le nombre total de colonnes dans la feuille (par exemple, 16384 pour Excel 2010+).
    • .End(xlToLeft) : Cette méthode se déplace vers la gauche à partir de la dernière colonne, s’arrêtant lorsqu’elle rencontre la première cellule non vide. Si la première ligne contient des données, cela reviendra à la première colonne contenant des données.
    • .Column : Cette méthode extrait le numéro de colonne de la dernière cellule contenant des données.

4. Afficher le résultat :

• MsgBox "La dernière colonne avec des données est : " & derniereColonne : Cette ligne affiche un message avec le numéro de la dernière colonne contenant des données.

Remarques supplémentaires :
1. Lignes vides dans la première ligne : L’approche ci-dessus vérifie la première ligne pour la dernière colonne avec des données. Si la première ligne est vide mais que des données sont présentes dans d’autres lignes, vous pouvez modifier le code pour vérifier une autre ligne ou utiliser la propriété UsedRange pour chercher dans toute la feuille.
Exemple pour vérifier l’ensemble de la feuille :
derniereColonne = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
2. Plage utilisée (UsedRange) : La propriété UsedRange fait référence à la plage de cellules utilisées (i.e., contenant des données). Cette méthode garantit que vous ne recherchez que dans la plage contenant des données, ce qui évite de scanner toute la feuille. Cependant, attention si des cellules ont été utilisées puis sont maintenant vides, car UsedRange pourrait inclure ces colonnes.
3. Cas particuliers :

• Si la première ligne est complètement vide, cette méthode fonctionnera toujours car elle commence par la dernière colonne et se déplace vers la gauche.
• Si des données sont présentes dans la colonne A, par exemple, mais pas dans la première ligne, vous devrez peut-être modifier le numéro de la ligne dans le code ou l’adapter en fonction d’autres critères.

Exemple avancé (Trouver la dernière colonne basée sur les données dans n’importe quelle ligne) :
Si vous souhaitez trouver la dernière colonne contenant des données dans n’importe quelle ligne, et pas seulement dans la première ligne, vous pouvez boucler sur les lignes ou vérifier toutes les colonnes d’une plage spécifique :

Sub TrouverDerniereColonneDuneAutreLigne()
    Dim derniereColonne As Long
    Dim ws As Worksheet
    Dim derniereLigne As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    ' Trouver la dernière ligne avec des données
    derniereLigne = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Trouver la dernière colonne dans la dernière ligne avec des données
    derniereColonne = ws.Cells(derniereLigne, ws.Columns.Count).End(xlToLeft).Column
    ' Afficher le résultat
    MsgBox "La dernière colonne avec des données est : " & derniereColonne
End Sub

Résumé :

• Le code fourni trouve la dernière colonne contenant des données dans la première ligne, en partant de la dernière colonne et en se déplaçant vers la gauche à l’aide de .End(xlToLeft).
• Cette méthode est efficace pour des dispositions de feuilles cohérentes.
• Pour des dispositions plus complexes, vous pouvez ajuster le code pour vérifier d’autres lignes ou plages, ou encore utiliser UsedRange pour plus de flexibilité.