Travailler avec des cellules et des plages dans Excel VBA (Select, Copy, Move, Edit)
lorsque vous travaillez avec Excel, la plupart de votre temps est consacré à la zone de la feuille de calcul – traiter les cellules et les plages.
et si vous souhaitez automatiser votre travail dans Excel en utilisant VBA, vous devez savoir comment travailler avec les cellules et les plages en utilisant VBA.
Il y a beaucoup de choses différentes que vous pouvez faire avec des plages en VBA (telles que sélectionner, copier, déplacer, modifier, etc.).
donc, pour couvrir ce sujet, je vais diviser ce tutoriel en sections et vous montrer comment travailler avec des cellules et des plages dans Excel VBA en utilisant des exemples.,
nous allons commencer.
Si vous souhaitez apprendre VBA de manière simple, consultez ma formation en ligne Excel VBA.
Ce Tutoriel Couvre:
Sélection d’une Cellule / Plage dans Excel en utilisant VBA
Pour travailler avec des cellules et des plages dans Excel en utilisant VBA, vous n’avez pas besoin de sélectionner.
dans la plupart des cas, il vaut mieux ne pas sélectionner de cellules ou de plages (comme nous le verrons).,
malgré cela, il est important que vous passiez par cette section et compreniez comment cela fonctionne. Cela sera crucial dans votre apprentissage VBA et de nombreux concepts abordés ici seront utilisés tout au long de ce tutoriel.
commençons donc par un exemple très simple.
sélection D’une seule cellule à L’aide de VBA
Si vous souhaitez sélectionner une seule cellule dans la feuille active (par exemple A1), vous pouvez utiliser le code ci-dessous:
Sub SelectCell()Range("A1").SelectEnd Sub
le code ci-dessus a les parties obligatoires » Sub » et « End Sub », et une ligne de code,
Range(« A1”) indique à VBA L’adresse de la cellule à laquelle nous voulons faire référence.
Select est une méthode de L’objet Range et sélectionne les cellules / plage spécifiées dans L’objet Range. Les références de cellule doivent être placées entre guillemets doubles.
ce code afficherait une erreur dans le cas où une feuille de graphique est une feuille active. Une feuille de graphique contient des graphiques et n’est pas largement utilisée. Comme il n’a pas de cellules/plages, le code ci-dessus ne peut pas le sélectionner et finirait par afficher une erreur.
Notez que, puisque vous voulez sélectionner la cellule dans la feuille active, il vous suffit de spécifier l’adresse de la cellule.,
Mais si vous voulez sélectionner la cellule dans une autre feuille (disons Feuil2), vous devez d’abord activer la Feuil2, puis sélectionnez la cellule en elle.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
de Même, vous pouvez également activer un classeur, puis activer une feuille de calcul spécifique, puis sélectionnez une cellule.
Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Notez que lorsque vous vous référez à des classeurs, vous devez utiliser le nom complet avec l’extension de fichier (.xlsx dans le code ci-dessus). Dans le cas où le classeur n’a jamais été enregistré, vous n’avez pas besoin d’utiliser l’extension de fichier.,
maintenant, ces exemples ne sont pas très utiles, mais vous verrez plus loin dans ce tutoriel comment nous pouvons utiliser les mêmes concepts pour copier et coller des cellules dans Excel (en utilisant VBA).
tout comme nous, sélectionnez une cellule, on peut également sélectionner une plage.
dans le cas d’une plage, il peut s’agir d’une plage de taille fixe ou d’une plage de taille variable.
dans une plage de taille fixe, vous sauriez Quelle est la taille de la plage et vous pouvez utiliser la taille exacte dans votre code VBA. Mais avec une plage de taille variable, vous n’avez aucune idée de la taille de la plage et vous devez utiliser un peu de magie VBA.
nous allons voir comment faire cela.,
sélection d’une plage de taille fixe
Voici le code qui sélectionnera la plage A1:D20.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
Une autre façon de procéder consiste à utiliser le code ci-dessous:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
le code ci-dessus prend l’adresse de la cellule en haut à gauche (A1) et l’adresse de la cellule en bas à droite (D20) et sélectionne Cette technique devient utile lorsque vous travaillez avec des plages de taille variable (comme nous le verrons lorsque la propriété End sera couverte plus loin dans ce tutoriel).,
Si vous souhaitez que la sélection se fasse dans un classeur différent ou une feuille de calcul différente, vous devez indiquer à VBA les noms exacts de ces objets.
Par exemple, le code ci-dessous sélectionnerait la plage A1:D20 dans la feuille de calcul Sheet2 dans le classeur Book2.
Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub
Maintenant, si vous ne savez pas combien de lignes sont là. Si vous souhaitez sélectionner toutes les cellules qui ont une valeur en elle.
dans ces cas, vous devez utiliser les méthodes indiquées dans la section suivante (sur la sélection d’une plage de taille variable).,
sélection d’une plage de taille variable
Il existe différentes façons de sélectionner une plage de cellules. La méthode que vous choisissez dépend de la façon dont les données sont structurées.
Dans cette section, je vais couvrir quelques techniques utiles qui sont vraiment utiles lorsque vous travaillez avec des plages en VBA.
Select Using CurrentRange Property
dans les cas où vous ne savez pas combien de lignes / colonnes ont les données, vous pouvez utiliser la propriété CurrentRange de L’objet Range.
la propriété CurrentRange couvre toutes les cellules remplies contiguës d’une plage de données.,
ci-dessous est le code qui sélectionnera la région actuelle qui contient la cellule A1.
Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub
la méthode ci-dessus est bonne lorsque vous avez toutes les données sous forme de table sans lignes/colonnes vides.
Mais si vous avez des lignes/colonnes vides dans vos données, il ne sélectionnera pas celles après les lignes / colonnes vides. Dans l’image ci-dessous, le code CurrentRegion sélectionne les données jusqu’à la ligne 10 car la ligne 11 est vide.
Dans de tels cas, vous souhaiterez peut-être utiliser la UsedRange propriété de l’Objet Feuille de calcul.,
Sélectionnez à l’Aide de UsedRange Propriété
UsedRange vous permet de consulter toutes les cellules qui ont été modifiées.
ainsi, le code ci-dessous sélectionnerait toutes les cellules utilisées dans la feuille active.
Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub
notez que dans le cas où vous avez une cellule éloignée qui a été utilisée, elle serait considérée par le code ci-dessus et toutes les cellules jusqu’à cette cellule utilisée seraient sélectionnées.
sélectionnez en utilisant la propriété End
maintenant, cette partie est vraiment utile.
la propriété End vous permet de sélectionner la dernière cellule remplie., Cela vous permet d’imiter l’effet de la touche Flèche de contrôle Bas/Haut ou des touches de contrôle Droite/Gauche.
essayons de comprendre cela en utilisant un exemple.
supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez sélectionner rapidement les dernières cellules remplies dans la colonne A.
le problème ici est que les données peuvent changer et que vous ne savez pas combien de cellules sont remplies. Si vous devez le faire en utilisant le clavier, vous pouvez sélectionner la cellule A1, puis utilisez ctrl + flèche Bas, et la dernière remplis de cellules dans la colonne.
voyons maintenant comment faire cela en utilisant VBA., Cette technique est utile lorsque vous souhaitez passer rapidement à la dernière cellule remplie dans une colonne de taille variable
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
le code ci-dessus sauterait à la dernière cellule remplie dans la colonne A.
de même, vous pouvez utiliser la fin(xlToRight) pour passer à la dernière cellule remplie d’une
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
Maintenant, si vous souhaitez sélectionner la colonne entière au lieu de sauter à la dernière remplis de cellules.,
Vous pouvez le faire en utilisant le code ci-dessous:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub
Dans le code ci-dessus, nous avons utilisé la première et la dernière référence de la cellule que nous avons besoin de sélectionner. Peu importe le nombre de cellules remplies, le code ci-dessus sélectionnera tout.
rappelez-vous l’exemple ci-dessus où nous avons sélectionné la plage A1:D20 en utilisant la ligne de code suivante:
Range(« A1″, »D20 »)
ici, A1 était la cellule en haut à gauche et D20 était la cellule en bas à droite de la plage. Nous pouvons utiliser la même logique pour sélectionner des plages de taille variable., Mais comme nous ne connaissons pas l’adresse exacte de la cellule en bas à droite, nous avons utilisé la propriété End pour l’obtenir.
dans la plage(« A1”, plage(« A1”).End(xlDown)), « A1” fait référence à la première cellule et à la plage(« A1”).End (xlDown) fait référence à la dernière cellule. Puisque nous avons fourni les deux références, la méthode Select sélectionne toutes les cellules entre ces deux références.
de même, vous pouvez également sélectionner un ensemble de données complet comportant plusieurs lignes et colonnes.
le code ci-dessous sélectionnerait toutes les lignes/colonnes remplies à partir de la cellule A1.,
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub
Dans le code ci-dessus, nous avons utilisé Range(« A1”).End(xlDown).End (xlToRight) pour obtenir la référence de la cellule remplie en bas à droite de l’ensemble de données.
différence entre L’utilisation de CurrentRegion et de End
Si vous vous demandez pourquoi utiliser la propriété End pour sélectionner la plage remplie lorsque nous avons la propriété CurrentRegion, laissez-moi vous dire la différence.
avec la propriété End, vous pouvez spécifier la cellule start., Par exemple, si vous avez vos données dans A1:D20, mais que la première ligne est constituée d’en-têtes, vous pouvez utiliser la propriété End pour sélectionner les données sans les en-têtes (en utilisant le code ci-dessous).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub
Mais CurrentRegion sélectionnerait automatiquement l’ensemble de données entier, y compris les en-têtes.
Jusqu’à présent, dans ce tutoriel, nous avons vu comment faire référence à une gamme de cellules de différentes manières.
voyons maintenant comment nous pouvons réellement utiliser ces techniques pour faire du travail.,
Copiez les cellules/plages en utilisant VBA
Comme je l’ai mentionné au début de ce tutoriel, la sélection d’une cellule n’est pas nécessaire pour effectuer des actions dessus. Vous verrez dans cette section Comment copier des cellules et des plages sans même les sélectionner.
commençons par un exemple simple.
Copie D’une cellule unique
Si vous souhaitez copier la cellule A1 et la coller dans la cellule D1, le code ci-dessous le ferait.
Sub CopyCell()Range("A1").Copy Range("D1")End Sub
notez que la méthode copy de l’objet range copie la cellule (tout comme Control +C) et la colle dans la destination spécifiée.,
dans l’exemple de code ci-dessus, la destination est spécifiée dans la même ligne où vous utilisez la méthode Copy. Si vous voulez rendre votre code encore plus lisible, vous pouvez utiliser le code ci-dessous:
Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub
Les codes ci-dessus copient et collent la valeur ainsi que la mise en forme/formules.
comme vous l’avez peut-être déjà remarqué, le code ci-dessus copie la cellule sans la sélectionner. Peu importe où vous êtes sur la feuille de calcul, le code copiera la cellule A1 et la collera sur D1.
notez également que le code ci-dessus écraserait tout code existant dans la cellule D2., Si vous voulez Qu’Excel vous dise s’il y a déjà quelque chose dans la cellule D1 sans l’écraser, vous pouvez utiliser le code ci-dessous.
copier une plage de taille fixe
Si vous souhaitez copier A1:D20 dans J1:M20, vous pouvez utiliser le code ci-dessous:
Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub
dans la cellule de destination, il vous suffit de spécifier l’adresse de la cellule en haut à gauche. Le code copierait automatiquement la plage copiée exacte dans la destination.
Vous pouvez utiliser la même construction pour copier les données d’une feuille à l’autre.
le code ci-dessous copierait A1:D20 de la feuille active vers Sheet2.,
Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
ce qui précède copie les données de la feuille active. Assurez-vous donc que la feuille contenant les données est la feuille active avant d’exécuter le code. Pour être sûr, vous pouvez également spécifier le nom de la feuille de calcul lors de la copie des données.
Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
la bonne chose à propos du code ci-dessus est que quelle que soit la feuille active, il copiera toujours les données de Sheet1 et les collera dans Sheet2.
Vous pouvez également copier une plage nommée en utilisant son nom au lieu de la référence.,
par exemple, si vous avez une plage nommée appelée ‘SalesData’, vous pouvez utiliser le code ci-dessous pour copier ces données dans Sheet2.
Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub
Si la portée de la plage nommée est le classeur entier, vous n’avez pas besoin d’être sur la feuille qui a la plage nommée pour exécuter ce code. Étant donné que la plage nommée est étendue pour le classeur, vous pouvez y accéder à partir de n’importe quelle feuille à l’aide de ce code.
Si vous avez une table avec le nom Table1, vous pouvez utiliser le code ci-dessous pour la Copier dans Sheet2.
Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub
Vous pouvez également copier une plage à l’autre Classeur.,
Dans l’exemple suivant, je copie le tableau Excel (Tableau 1), dans le Livre2 classeur.
Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub
Ce code ne fonctionne que si le Classeur est déjà ouvert.
copier une plage de taille Variable
Une façon de copier des plages de taille variable consiste à les convertir en plages nommées ou en tableau Excel et à utiliser les codes comme indiqué dans la section précédente.
Mais si vous ne pouvez pas le faire, vous pouvez utiliser la CurrentRegion ou la propriété End de l’objet range.
le code ci-dessous copierait la région actuelle dans la feuille active et la collerait dans Sheet2.,
Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub
Si vous souhaitez copier la première colonne de votre ensemble de données jusqu’à la dernière cellule remplie et la coller dans Sheet2, vous pouvez utiliser le code ci-dessous:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub
Si vous souhaitez copier les lignes ainsi que les colonnes, vous pouvez utiliser le code e652268038″>
notez que tous ces codes ne sélectionnent pas les cellules lors de l’exécution. En général, vous ne trouverez que quelques cas où vous devez réellement sélectionner une cellule / plage avant de travailler dessus.,
assigner des plages aux Variables D’objet
Jusqu’à présent, nous avons utilisé l’adresse complète des cellules (telles que les classeurs(« Book2.xlsx »).Feuilles De Calcul (”Sheet1″).Gamme (« A1 »)).
pour rendre votre code plus gérable, vous pouvez affecter ces plages aux variables d’objet, puis utiliser ces variables.
par exemple, dans le code ci-dessous, j’ai affecté la plage source et la plage de destination aux variables d’objet, puis utilisé ces variables pour copier des données d’une plage à l’autre.
nous commençons par déclarer les variables en tant qu’objets Range., Ensuite, nous affectons la plage à ces variables en utilisant L’Instruction Set. Une fois que la gamme a été affectée à la variable, vous pouvez simplement utiliser la variable.
Entrer les Données dans la Prochaine Cellule Vide (en Utilisant la Zone de Saisie)
Vous pouvez utiliser les zones de Saisie pour permettre à l’utilisateur d’entrer les données.
par exemple, supposons que vous ayez l’ensemble de données ci-dessous et que vous souhaitiez entrer l’enregistrement de vente, vous pouvez utiliser la zone de saisie dans VBA. À l’aide d’un code, nous pouvons nous assurer qu’il remplit les données dans la ligne vide suivante.,
le code ci-dessus utilise la zone de saisie VBA pour obtenir les entrées de l’utilisateur, puis entre les entrées dans les cellules spécifiées.
notez que nous n’avons pas utilisé de références de cellules exactes. Au lieu de cela, nous avons utilisé la propriété End et Offset pour trouver la dernière cellule vide et y remplir les données.
Ce code est loin d’être utilisables. Par exemple, si vous entrez une chaîne de texte lorsque la zone de saisie demande quantité ou montant, vous remarquerez Qu’Excel le permet. Vous pouvez utiliser une condition If pour vérifier si la valeur est numérique ou non, puis l’autoriser en conséquence.,
en parcourant les cellules/plages
Jusqu’à présent, nous avons pu voir comment sélectionner, Copier et entrer les données dans les cellules et les plages.
dans cette section, nous verrons comment parcourir un ensemble de cellules/lignes/colonnes dans une plage. Cela pourrait être utile lorsque vous souhaitez analyser chaque cellule et effectuer une action en fonction de celle-ci.
par exemple, si vous souhaitez mettre en surbrillance chaque troisième ligne de la sélection, vous devez parcourir et vérifier le numéro de ligne., De même, si vous souhaitez mettre en évidence toutes les cellules négatives en changeant la couleur de la police en rouge, vous devez parcourir et analyser la valeur de chaque cellule.
Voici le code qui parcourra les lignes dans les cellules sélectionnées et mettra en évidence les lignes alternatives.
Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub
Le code ci-dessus utilise la fonction MOD pour vérifier le numéro de ligne dans la sélection. Si le numéro de ligne est pair, il est mis en surbrillance en couleur cyan.
Voici un autre exemple où le code passe par chaque cellule et met en évidence les cellules qui ont une valeur négative.,
Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub
Notez que vous pouvez faire la même chose en utilisant la mise en forme Conditionnelle (qui est dynamique et une meilleure façon de le faire). Cet exemple est uniquement dans le but de vous montrer comment le bouclage fonctionne avec les cellules et les plages dans VBA.
où mettre le Code VBA
Vous vous demandez où va le code VBA dans votre classeur Excel?
Excel a un backend VBA appelé l’éditeur VBA. Vous devez copier et coller le code dans la fenêtre de code du module VB Editor.
Voici les étapes pour ce faire:
- Allez à l’onglet Développeur.,
- Cliquez sur le Visual Basic option. Cela ouvrira L’éditeur VB dans le backend.
- dans le volet Explorateur de projets de L’éditeur VB, faites un clic droit sur n’importe quel objet du classeur dans lequel vous souhaitez insérer le code. Si vous ne voyez pas L’Explorateur de projets, accédez à L’onglet Affichage et cliquez sur Explorateur de projets.
- allez dans insérer et cliquez sur Module. Cela va insérer un objet module pour votre classeur.
- copiez et collez le code dans la fenêtre du module.,
Vous pouvez également aimer les didacticiels Excel suivants:
- travailler avec des feuilles de calcul en utilisant VBA.
- travailler avec des classeurs en utilisant VBA.
- création de fonctions définies par L’utilisateur dans Excel.
- Pour la boucle suivante dans Excel VBA-un guide du débutant avec des exemples.
- Comment utiliser la fonction Excel VBA InStr (avec des exemples pratiques).
- Excel VBA Msgbox.
- Comment Enregistrer une Macro dans Excel.
- Comment Exécuter une Macro dans Excel.
- Comment créer un complément dans Excel.,
- Excel Classeur de Macros Personnelles | Enregistrer & Utiliser des Macros dans Tous les Classeurs.
- Excel VBA Events – un Guide facile (et complet).
- Gestion des erreurs Excel VBA.
- comment trier les données dans Excel en utilisant VBA (un Guide étape par étape).
- 24 exemples de Macro Excel utiles pour les débutants VBA (prêts à l’emploi).