Contrôle de feuille de calcul avec VBA UsedRange - wellsr.com (2023)

La propriété VBA UsedRange est une propriété pure ajoutée à la bibliothèque VBA. Couramment trouvé dans les fils de discussion Web pour trouver le nombre de lignes et de colonnes dans une feuille - et compter les itérations d'une boucle for par proxy - ce qui est certainement utile.plage d'utilisationIl y a quelques autres propriétés intéressantes et quelques mises en garde à garder à l'esprit. Voyons.

• Objet UsedRange
• forme de série
• Utiliser des lignes et des colonnes
  • Utilisation de la propriété .Address
  • première ligne première colonne
• compter les lignes et les colonnes
  • dernière ligne et dernière colonne
  • Pour le nombre de boucles
• propriétés et méthodes
  • gamme claire
• plage de navigation
• changements cachés

plage d'utilisationBut

plage d'utilisationest spécialchamps d'applicationDéfinit les propriétés de la plage de cellules utilisée dans la feuille de calcul. En fait, certaines modifications apportées à une cellule la marquent également comme "utilisée" selon VBA. Nous en discuterons en détail dans quelques minutes, mais une meilleure description de la propriété VBA UsedRange estSpécifie une plage de cellules dans une feuille de calcul dont le contenu, la mise en forme, les commentaires ou toute autre modification.C'est un excellent moyen de mieux contrôler les données de votre feuille de calcul.

Depuisplage d'utilisationoui fonctionnalitéchamps d'applicationobjet, toutes les propriétés et méthodes disponibles sur le champ sont également disponibles sur l'objetplage d'utilisationCela inclut le comptage des colonnes/lignes, la sélection, l'effacement, le formatage et la navigation dans les plages. De plus, vous pouvez en faire un objet nommé pour une référence plus facile, par exemple :

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisation

Oùtableau cibleest le nom de la feuille de calcul que vous souhaitez utiliser.

Ensemble d'objets nommés, par ex.rng, vous donnant accès àIntelliSense, un outil précieux.Notez que l'objet n'est pas mis à jour immédiatement, vous devrez donc le configurer à nouveau pour obtenir les propriétés mises à jour !si vous définissezrngpuis changez quelque chose comme l'application. nettoyerméthode, votre variablerngJe vaisNonMises à jour automatiques. Vous devez le réinitialiser.

Pour montrer VBA UsedRange en action, ajoutez quelques valeurs à une feuille de calcul vierge et exécutez cette courte macro :

Enfant Sélectionnez le domaine d'utilisation() feuille d'activité.plage d'utilisation.choisirfin Enfant

Cette macro sélectionne la plage utilisée dans la feuille de calcul. Vous pouvez voir que VBA essaie d'utiliser les données de la feuille de calcul pour calculer la première et la dernière cellule et sélectionner tout dans cette plage. Notez que dans le diagramme ci-dessous, toutcellule videIl est également inclus dans la plage utilisée lors de l'utilisation de la propriété UsedRange. Bien que vous pensiez que ce serait le cas, la propriété VBA UsedRange ne se contente pas d'enregistrerALLIANCEToutes les cellules contenant des données.

forme de série

Comme nous venons de le montrer, VBA ne permet pas de choisir la forme de la ligne masquéeplage d'utilisation.Crée toujours un rectangle et commence dans le coin supérieur gauche et s'ouvre pour capturer la ligne la plus basse et la colonne de droite. Bien qu'elles définissent les limites de UsedRange, les cellules supérieures gauche et inférieure droite peuvent ne contenir aucune valeur.

Par exemple,plage d'utilisationSur cette photo c'estB1 : E25, bien que les quatre cellules dans les coins supérieurs, y compris la celluleB1, ne contient aucune donnée ni mise en forme. ni cellulesQ25.Il en va de même pour les quatre cellules dans le coin inférieur gauche. Il est important de noter que la ligne vide entre les premières et les dix entrées suivantes, la ligne 13, est également comptée - gardez cela à l'esprit lorsque vous utilisez UsedRange de VBA comme base pour le nombre de lignes et de colonnes d'une macro !

UsedRange et cellules non remplies

Créez des macros puissantes avec le kit de développement VBA gratuit

C'est vraiment chouette. Si vous avez des difficultés à comprendre ou à vous souvenir,Kit de développement VBApuis-je aider. Il est chargé de raccourcis VBA pour vous aider à créer vos propres macros comme celle-ci - nous vous enverrons une copie avecGrand Livre Excel VBA, à votre adresse e-mail ci-dessous.

Utiliser des lignes et des colonnes

plage d'utilisationTrouvez facilement la première et la dernière ligne et colonne utilisées dans une feuille de calcul. Par extension, il peut être utilisé pour compter le nombre de lignes et de colonnes qui aiderontitérer sur la boucle for.

utiliser. adressepropriété

Une façon d'obtenir la première paire ligne/colonne et la dernière paire ligne/colonne consiste à utiliser. adressePropriétés d'UsedRange :

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationdébogage.Imprimer rng.adresse

Cela renverra la chaîne$B$1 :$E$25, qui peut être décomposé en parties nécessaires de la première rangée (1), de la première colonne (B), de la dernière rangée (25) et de la dernière colonne (E).N'oubliez pas que Debug.Print imprime sur votreFenêtre VBA directe.

première ligne première colonne

Vous pouvez aussi utiliser. Sérieet. Colonnecaractéristique:

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationdébogage.Imprimer rng.Sériedébogage.Imprimer rng.Colonne

Cela renvoie simplement les premiers numéros de ligne et de colonne de la plage (1 et 2 dans notre exemple), correspondant à1 G$Cellules dont nous avons parlé plus tôt.

le numéro estdernierLes lignes et les colonnes nécessitent plus de travail. Une façon d'obtenir la dernière ligne et colonne de UsedRange consiste à utilisernumérotagepropriété.

compter les lignes et les colonnes

Pour obtenir le nombre de lignes et de colonnes dans une plage, vous pouvez utiliser. numérotage

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationdébogage.Imprimer rng.Nombre de lignes.numérotagedébogage.Imprimer rng.Liste.numérotage

Dans notre exemple ci-dessus, cela renverrait 25 et 4.

dernière ligne et dernière colonne

Si la première cellule de UsedRange est$1 $A, trouver la dernière ligne et colonne utilisée est simple car. numérotageLe nombre de lignes et de colonnes est égal à la dernière ligne et colonne que vous avez utilisées.

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationla dernière ligne = rng.Nombre de lignes.numérotage '25dernière rangée = rng.Liste.numérotage '4

Cependant ce n'est pas souvent le cas.Si vos données ne proviennent pas de$1 $A, cette méthode ne fonctionnera pas !

Comme vous pouvez le voir sur notre image d'exemple, la dernière ligne est en effet la ligne 25. Cependant, la dernière colonne estNonColonne 4 (D). Il s'agit de la colonne 5 (E). Pour être plus puissant, il faut ajouter le numéro de la première ligne/colonne au nombre de lignes/colonnes.

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationpremière rangée = rng.Sériepremière rangée = rng.ColonneNombre de lignes = rng.Nombre de lignes.numérotageLe nombre de colonnes = rng.Liste.numérotage"N'oubliez pas de soustraire 1 pour éviter de compter deux fois la première ligne/colonnela dernière ligne = première rangée + Nombre de lignes - 1dernière rangée = première rangée + Le nombre de colonnes - 1

Nous avons maintenant le résultat attendu {25, 5} au lieu de {25, 4}. N'oubliez pas de soustraire 1 du résultat final, sinon vous compterez deux fois la première ligne et la première colonne.

Maintenant que nous avons montré la logique de calcul de la dernière ligne et colonne utilisée dans UsedRange,Laissez-nous vous montrer un moyen plus simple.Pour enregistrer la dernière ligne et colonne utilisée dans la plage utilisée, utilisez simplement le raccourci suivant :

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationla dernière ligne = rng.Nombre de lignes(rng.Nombre de lignes.numérotage).Sériedernière rangée = rng.Liste(rng.Liste.numérotage).Colonne

Pour le nombre de boucles

Notez que nous continuons à utiliser. numérotagepropriété. Ce. numérotageLes attributs sont également utiles lors de la création de boucles. Commencez par la première ligne/colonne et traitez toute la plage jusqu'à la dernière ligne/colonne. Pour une approche simplifiée qui ne fait que parcourir les lignes, supposons que la feuille de calcul ne contienne que des données numériques dans la colonne A. Vous pouvez stocker un ensemble actuel de nombres dans la colonne B (colonne 2) à l'aide d'une macro comme celle-ci :

Enfant total cumulé()faible première rangée comme entier, la dernière ligne comme entier, ligne je comme entierfaible rng comme champs d'applicationje mets rng = feuille d'activité.plage d'utilisationpremière rangée = rng.Sériela dernière ligne = rng.Nombre de lignes(rng.Nombre de lignes.numérotage).SériePour ligne je = première rangée atteindre la dernière ligne si ligne je = première rangée après cellule(ligne je, 2) = cellule(ligne je, 1) "Commencez avec un ensemble en cours d'exécution mais cellule(ligne je, 2) = cellule(ligne je, 1) + cellule(ligne je - 1, 2) "Ajoute le jeu d'exécution précédent à la nouvelle entrée fin siSuivant ligne jefin Enfant

Vous aurez besoin de deux boucles imbriquées pour parcourir les lignes et les colonnes de la plage que vous utilisez, comme ceci :

Enfant LoopThroughUsedRange()faible première rangée comme entier, la dernière ligne comme entierfaible première rangée comme entier, dernière rangée comme entierfaible ligne je comme entier, iCol comme entierfaible rng comme champs d'applicationje mets rng = feuille d'activité.plage d'utilisation "Stocker la plage utilisée dans une variablepremière rangée = rng.Sériepremière rangée = rng.Colonnela dernière ligne = rng.Nombre de lignes(rng.Nombre de lignes.numérotage).Sériedernière rangée = rng.Liste(rng.Liste.numérotage).ColonnePour iCol = première rangée atteindre dernière rangée Pour ligne je = première rangée atteindre la dernière ligne débogage.Imprimer cellule(ligne je, iCol).adresse & " = " & cellule(ligne je, iCol) Suivant ligne jeSuivant iColfin Enfant

Si vous souhaitez modifier les données uniquement dans les cellules de UsedRange qui ne sont pas vides, appliquezFonction VBA IsEmpty.

propriétés et méthodes

Une fois que vous avez appris à naviguer dans UsedRange, l'application de propriétés à l'aide de VBA est un jeu d'enfant. Les fonctionnalités vous permettent de faire des choses comme changer une zone entière en gras. Notez que les attributs seront appliqués àtousplage, même si la cellule est vide. Revenir en arrière et corriger la mise en forme d'une feuille entière à cause d'une seule mauvaise ligne de code serait fastidieux.

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationrng.Police de caractère.audacieux = réel

Cette section macro est en train de changertoutes les cellulesÉcriture en gras sur des feuilles contenant des données (ou mise en forme plus cellules vides entre les cellules utilisées). Vous pouvez faire la même chose avec la couleur, la couleur de remplissage des cellules,Format de nombreAttendez. Notez à nouveau que cette propriété s'appliquera à toute la plage d'utilisation, donc si vous avez un tableau bien formaté avec 5000 cellules, assurez-vous de mettre toutes les 5000 cellules en gras avant d'exécuter ces lignes de code !

gamme claire

Une autre utilisation très couranteplage d'utilisationc'est pourSupprimer le contenu de la feuille de calcul.Lorsque vous utilisez UsedRange, vous n'avez pas besoin de trouver la dernière ligne/colonne comme nous l'avons fait ci-dessus. Après réglagerngEn tant qu'objet nommé, faites simplement :

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationrng.nettoyer ' ou rng.ClearContents efface simplement les données

Assurez-vous à nouveau que vous voulez le supprimertousFormat de données et feuille de calcul. comme utilisersudo rm-rSous Linux : Puissant et pratique, mais dangereux.

Créez des macros puissantes avec le kit de développement VBA gratuit

C'est vraiment chouette. Si vous avez des difficultés à comprendre ou à vous souvenir,Kit de développement VBApuis-je aider. Il est chargé de raccourcis VBA pour vous aider à créer vos propres macros comme celle-ci - nous vous enverrons une copie avecGrand Livre Excel VBA, à votre adresse e-mail ci-dessous.

plage de navigation

La portée permet l'accèspar rapport à la gamme elle-mêmeplutôt que par rapport à la feuille de calcul. Par exemple, dans notre tableau ci-dessus, la première colonne est en fait B, pas A. Si nous connaissons la disposition des colonnes du tableau - nom de l'axe, rang, pays, population - nous pouvons nous référer àpopulationPar rapport à la feuille de calcul ou par rapport à la plage.

• Par rapport à la colonne Population de la feuille de travail = 5
• Colonne de population vs plage utilisée = 4

Supposons que l'utilisateur déplace la table vers une position différente dans la feuille de calcul pour une raison quelconque. Si vous aviez codé la macro pour obtenir la population par rapport à la feuille de calcul, vous pourriez maintenant obtenir par inadvertance les mauvaises données. Il est plus robuste d'utiliser la structure du tableau lui-même comme référence :

faible rng comme champs d'applicationje mets rng = drap de lit("tableau des buts").plage d'utilisationrng.Liste(4).Police de caractère.audacieux = réel

Par exemple, si l'utilisateur déplace le début du tableau à $H$15, nous pouvons mettre la colonne de population en gras car la colonne de population sera toujours la colonne 4 dans UsedRange.

changements cachés

Il y a au moins une mise en garde en cours d'utilisationplage d'utilisationC'est une question importante à considérer. Les avertissements font référence à des modifications invisibles. Les cellules qui ont été modifiées de manière invisible (telles que la mise en forme des nombres, les changements de police ou même les commentaires) seront incluses dans la couverture UsedRange. Cela signifie que si l'utilisateur met accidentellement en italique une cellule sans données, cette cellule compte toujours comme une "plage utilisée", de sorte que UsedRange peut être plus élevé que prévu. Cette extension apparaît lorsqu'elle est sélectionnéeplage d'utilisationseront inclus dans. numérotageet. adressecaractéristique.

Ce n'est pas toujours un problème, mais cela peut créer des problèmes qui sont facilement négligés. chez noustotal cumuléPar exemple, si seules les 10 premières cellules contiennent des données, il souhaite que le total atteigne la ligne 10. Cependant, si l'utilisateur met accidentellement en italique la cellule23 $ D $Balayez sur le clavier etCtrl+i, nous obtenons cet étrange résultat dans la colonne B, qui ne manquera pas de dérouter les utilisateurs :

Les modifications de format affectent la plage d'utilisation de VBA

Les cellules n'ont pas beaucoup changé23 $ D $, maisplage d'utilisationEnvisagez de modifier la mise en forme et incluez des régions entières$1 $Aatteindre23 $ D $.

plage d'utilisationTrouvez facilement la première ligne/colonne et appliquez la mise en forme à n'importe quelle cellule de feuille de calcul utile. Cela facilite également la recherche de la dernière ligne / colonne et, plus important encore, vous aide à configurer des boucles.

Si vous avez trouvé ce didacticiel utile, abonnez-vous en utilisant le formulaire ci-dessous et nous partagerons des conseils VBA similaires pour vous aider à tirer le meilleur parti du langage de programmation.