[VIDEO]Comment ajuster automatique la taille cellule en fonction de son contenu

Aujourd’hui, nous allons voir comment procéder pour ajuster automatiquement la taille des cellules en fonction du contenu saisi dans celles-ci. Pour commencer, nous allons voir comment procéder de manière manuelle, puis dans un second temps nous verrons comment faire pour que la taille des cellules s'ajuste automatiquement lorsque le contenu de celle-ci va être modifié.

 

Partie 1 :

Partie 2 :

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier dans le lien disponible en fin d’article (cliquez ici pour y accéder).

 

Téléchargement

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

 

 

 

1. Présentation

 

Pour illustrer cet exemple nous allons utiliser le fichier que vous avez sous les yeux, constitué d'une base de données qui se trouve dans l’onglet « Base ». Dans celle-ci, nous allons retrouver des articles avec leur prix respectifs, ainsi que leurs caractéristiques.

Excel formation - Ajuster la taille de cellule - 01

Dans un premier temps, le but va être de récupérer le prix et les caractéristiques d’un article dont nous allons insérer la référence dans la première cellule ($B$7).

Pour éviter d'avoir à ressaisir les codes de nos articles nous allons utiliser la « Validation de données ».  Pour cela nous allons dans l'onglet « Données », puis cliquer sur « Validation de données ».

Excel formation - Ajuster la taille de cellule - 02

Nous allons faire le choix d’autoriser une « Liste » :

Excel formation - Ajuster la taille de cellule - 03

Puis, nous allons simplement venir choisir la première colonne de la base de données : « Modèle ». Notre base de données était présentée sous la forme d’un tableau de données, il nous suffit simplement de venir cliquer sur l'en-tête de la colonne, (lorsque la flèche pointe vers le bas) pour sélectionner automatiquement toute la colonne.

Excel formation - Ajuster la taille de cellule - 04

Par la suite, lorsque nous ajouterons de nouvelles lignes dans notre base de données, Excel adaptera automatiquement la liste pour y intégrer ces nouvelles informations.

Pour en savoir plus sur l’utilisation des tableaux de données, je vous invite à consulter cet article.

Nous pouvons à présent valider la sélection en appuyant sur la touche [Entrée] du clavier.

De cette manière, nous avons bien à disposition un menu déroulant qui va nous permettre de venir saisir la référence de l’article qui nous intéresse.

Excel formation - Ajuster la taille de cellule - 05

À présent, pour obtenir les informations sur l’article choisi, nous allons utiliser les formules imbriquées INDEX() et EQUIV().

Nous allons simplement saisir les deux formules suivantes dans les cellules B8 et B9 :

Formule à insérer dans la cellule B8 :

INDEX(_baseArticles[Prix];EQUIV(B7;_baseArticles[Modèle];))

 

Formule à insérer dans la cellule B9 :

 

 =INDEX(_baseArticles[Caractéristiques];EQUIV(B7;_baseArticles[Modèle];))

 

Excel formation - Ajuster la taille de cellule - 06

 

Nous passons rapidement sur l’utilisation de ces formules, mais si vous souhaitez en savoir davantage, vous trouverez toutes les informations dans cet article.

Enfin, lorsque nous consultons la base de données d'origine, nous pouvons observer l’existence d’un retour à la ligne à la fin de chaque caractéristique. Pour en faire de même dans la cellule contenant la formule, nous allons nous rendre dans le menu « Accueil » du ruban, puis sélectionner « Retour à la ligne automatique ».

Excel formation - Ajuster la taille de cellule - 07

Excel formation - Ajuster la taille de cellule - 08

Nous allons à présent en venir au sujet qui nous intéresse, à savoir comment modifier la hauteur et la largeur d’une cellule.

 

2. La méthode manuelle

 

Si l’on regarde la cellule qui nous retourne les caractéristiques de notre machine, nous pouvons constater que celle-ci est plus grande que son contenu.

Excel formation - Ajuster la taille de cellule - 09Pour réduire l’espace ainsi perdu, nous pouvons simplement modifier manuellement la largeur de la colonne, en glissant le curseur qui apparaît lorsque l’on place la souris entre deux en-têtes de colonne, vers la gauche pour réduire la largeur de colonne, ou vers la droite pour l’augmenter.

Excel formation - Ajuster la taille de cellule - 10

Excel formation - Ajuster la taille de cellule - 11

Lorsque nous sélectionnons un autre modèle, nous constatons que les caractéristiques changent de manière automatique. Ainsi la taille de la cellule que nous venons juste d’ajuster est à présent trop étroite.

Excel formation - Ajuster la taille de cellule - 12

Nous avons ici une ligne qui se retrouvent sur deux lignes. Il va donc falloir agrandir la largeur de cette cellule pour que les informations se trouvent sur une seule ligne.

Excel formation - Ajuster la taille de cellule - 13

Sachez également qu’il existe un raccourci pour adapter automatiquement la hauteur d’une ligne ou la largeur d’une colonne à son contenu : il suffit de double cliquer sur le curseur qui apparaît lorsque l'on se place entre deux numéros de lignes (ou deux lettres de colonnes).

Excel formation - Ajuster la taille de cellule - 14

Excel formation - Ajuster la taille de cellule - 15

Malheureusement ce raccourci souffre d’un « bug » un peu gênant : lorsque que la cellule possède beaucoup de caractères sur une seule ligne, celle-ci ne va pas s’élargir suffisamment pour afficher toutes les informations sur une seule ligne de texte.

Excel formation - Ajuster la taille de cellule - 16

Il est alors nécessaire de donner une grande largeur à la colonne, puis de double cliquer sur l’entre-deux colonnes pour ajuster automatiquement le contenu. En fait, l’agrandissement automatique d’une colonne est limité à une certaine taille.

Excel formation - Ajuster la taille de cellule - 17

Excel formation - Ajuster la taille de cellule - 18

Cette méthode, dite « manuelle » permet de rapidement adapter la taille des cellules à leur contenu. Cela dit, elle nécessite malgré tout une manipulation. Nous allons maintenant voire comment procéder pour arriver au même résultat, avec l’aide d’une une macro-commande qui se lancera de manière automatique et totalement transparente.

 

3. La méthode automatisée

 

Pour modifier automatiquement la taille des cellules, que cela soit en hauteur, ou encore en largeur, nous allons utiliser une macro-commande développée en VBA.

Pour cela nous allons nous rendre dans le code source de la page, en effectuant un clic droit sur l'onglet de la feuille, puis en sélectionnant « Visualiser le code ».

Excel formation - Ajuster la taille de cellule - 19

Ainsi nous arrivons dans l'outil développement d'Excel, qui comme nous venons de le voir porte le nom de « Microsoft Visual Basic pour Applications », ou encore « VBA ».

 

Excel formation - Ajuster la taille de cellule - 20

Dans cette feuille blanche nous allons insérer une procédure événementielle, c’est-à-dire un ensemble d’instructions qui vont être exécutées à chaque fois qu’un évènement bien particulier va se produire. L’événement qui nous intéresse ici est « une cellule de la feuille de calcul a été modifiée », soit en langage VBA : « Worksheet_Change ».

Pour insérer cette procédure évènementielle, nous allons sélectionner dans le menu déroulant se trouvant à gauche au-dessus de la feuille de code « Worksheet ».

Excel formation - Ajuster la taille de cellule - 21

Puis l’événement « Change » dans le second menu situé juste à droite du premier :

Excel formation - Ajuster la taille de cellule - 22

De cette manière, Excel ajoute automatiquement pour nous l’événement qui nous intéresse.

Excel formation - Ajuster la taille de cellule - 23

À présent, à chaque fois qu’une cellule de la feuille de calcul va être modifiée, l’ensemble des instructions que nous allons saisir entre ces deux lignes vont être appelées automatiquement.

De plus, les cellules concernées (dont la valeur est modifiée) vont être retourné dans la variable instanciée et qui porte le nom de « Target ».

Pour commencer, nous allons ajuster la largeur des colonnes dans lesquelles se trouve une cellule modifiée. Pour cela nous allons utiliser l’instruction « Autofit » appliquée sur toute la colonne dans laquelle se trouve la cellule « Target » (Target.EntireColumn) de la manière suivante :

 

Private Sub Worksheet_Change(ByVal Target As Range)


    Target.EntireColumn.AutoFit


End Sub

 

 

 

Excel formation - Ajuster la taille de cellule - 24

L’ « Autofit » correspond au double-clic qui ajuste automatiquement la largeur d’une colonne ou la hauteur d’une ligne, et que nous avons dans la partie précédente de cet article.

Faisons de même pour la hauteur de ligne :

 

Private Sub Worksheet_Change(ByVal Target As Range)


    Target.EntireColumn.AutoFit


    Target.EntireRow.AutoFit


End Sub

 

Excel formation - Ajuster la taille de cellule - 25

 

 

Rappelez-vous, dans la partie précédente, nous avions vu que l’ « Autofit » ne pouvait pas agrandir indéfiniment la largeur d’une colonne. Pour contourner cette limitation, nous allons dans un premier temps donner une grande largeur (d’une taille de 255, soit la taille maximale qu’une colonne puisse avoir), afin que l’ « Autofit » se contente de la diminuer.

 

Private Sub Worksheet_Change(ByVal Target As Range)


    Target.EntireColumn.ColumnWidth = 255


    Target.EntireColumn.AutoFit


    Target.EntireRow.AutoFit


End Sub

 

 

 

Enfin, au lieu d’appliquer ces modifications uniquement sur la cellule modifiée, nous allons utiliser l’ensemble des cellules de la région concernée (c’est-à-dire les cellules du tableau).

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target.CurrentRegion

        .EntireColumn.ColumnWidth = 255
        .EntireColumn.AutoFit
        .EntireRow.AutoFit


    End With

End Sub

 

Excel formation - Ajuster la taille de cellule - 26

 

Pour simplifier la lecture du code VBA, nous avons également utilisé l’instruction « with » sur « Target.CurrentRegion ». Cela signifie que chaque instruction qui commence par un point (« . ») sera appliquer sur celle-ci. Le code ci-dessus est ainsi équivalent à :

 

Private Sub Worksheet_Change(ByVal Target As Range)

        Target.CurrentRegion.EntireColumn.ColumnWidth = 255
        Target.CurrentRegion.EntireColumn.AutoFit
        Target.CurrentRegion.EntireRow.AutoFit

End Sub

 

 

 

Si nous testons notre procédure, nous pouvons constater qu'à présent la hauteur va s'adapter pour l'ensemble de nos cellules du tableau.

Excel formation - Ajuster la taille de cellule - 27

Excel formation - Ajuster la taille de cellule - 28

Excel formation - Ajuster la taille de cellule - 29

Excel formation - Ajuster la taille de cellule - 30

Excel formation - Ajuster la taille de cellule - 31

 

4. Ajouter l’autofit sur une plage de cellules (ajustement automatique de la taille) sur Excel

 

Supposons maintenant que nous souhaitions ajouter l’ajustement automatique de la taille des cellules, non plus sur une cellule, mais sur une plage contenant un ensemble de cellules que nous allons prédéfinir.

Pour illustrer cet exemple, nous allons souhaiter que la taille des cellules de la base des articles s’ajuste à chaque modification.

Pour commencer, nous allons copier la macro-commande que nous venons de saisir dans le code de la feuille Taille de cellule :

Excel formation - Autofit sur une plage de cellules - 01

Pour la coller dans la feuille Base :

Excel formation - Autofit sur une plage de cellules - 02

De cette manière, tous les changements que nous allons effectuer dans la feuille de calculs Base vont avoir pour conséquence de recalculer la taille des cellules :

Excel formation - Autofit sur une plage de cellules - 03

Excel formation - Autofit sur une plage de cellules - 04

Y compris, lorsque les modifications sont effectuées en dehors de la base des articles :

Excel formation - Autofit sur une plage de cellules - 05

Pour éviter ce problème, nous allons souhaiter limiter l’autofit des cellules à la plage de la base des données.

Si l’on regarde attentivement les coordonnées de la base des articles, cette dernière commence à partir de la cellule A6, jusqu’à la cellule C11.

Nous allons donc modifier la macro-commande pour vérifier que la cellule modifiée fait bien partie de cette plage de cellules. Pour cela, nous allons utiliser l’instruction INTERSECT de VBA, qui permet de récupérer les cellules communes aux plages de cellules saisies en paramètres. Lorsqu’il n’existe aucune cellule de commune, alors l’instruction INTERSECT renvoie comme valeur nothing. Il suffit alors de quitter l’instruction :

 Private Sub Worksheet_Change(ByVal Target As Range)

     If Intersect(Target, [a6:c11]) Is Nothing Then Exit Sub

     With Target.CurrentRegion
         .EntireColumn.ColumnWidth = 255
         .EntireColumn.AutoFit
         .EntireRow.AutoFit
     End With

 End Sub

De cette manière, nous sommes certains que le code VBA passe en dessous de la ligne que nous venons d’ajouter, alors cela signifie que la cellule modifiée (Target) fait bien partie de la plage des cellules A6 :C11.

Excel formation - Autofit sur une plage de cellules - 06

En revanche, si d’aventure nous souhaitons ajouter une nouvelle ligne à la des articles, le résultat sera le même, et les cellules ne vont pas voir leur taille évoluer :

Excel formation - Autofit sur une plage de cellules - 07

Nous pourrions définir une plage de cellules plus importante (par exemple A6:C100), mais dans ce cas nous perdrons le dynamisme de la macro.

Nous allons plutôt utiliser une plage nommée, qui aura comme avantage de s’adapter automatiquement aux coordonnées de la plage des cellules.

De plus, étant donné que nous utilisons un tableau de données, cette plage existe déjà, nous n’avons qu’à la récupérer (en cliquant sur une des cellules du tableau, puis menu Création du ruban > Récupérer le nom que nous avions défini précédemment : « _baseArticles ») :

Excel formation - Autofit sur une plage de cellules - 08

Il suffit ensuite d’ajouter simplement le nom de la directement dans la macro, au lieu des coordonnées a6:c11 :

 

 Private Sub Worksheet_Change(ByVal Target As Range)

     If Intersect(Target, [_baseArticles]) Is Nothing Then Exit Sub

     With Target.CurrentRegion

         .EntireColumn.ColumnWidth = 255

         .EntireColumn.AutoFit

         .EntireRow.AutoFit

     End With

 End Sub

Et voilà le résultat :

Excel formation - Autofit sur une plage de cellules - 09

Enfin, nous allons voir une dernière solution, qui va consister à réajuster les tailles des cellules contenues dans une plage de cellules, même lorsque la cellule mise-à-jour ne fait pas partie de la plage.

Pour commencer, nous supprimons la ligne que nous venons d’ajouter. En effet, à présent la macro doit pouvoir s’exécuter, peu importe de savoir si la cellule appartient ou non à la plage :

 Private Sub Worksheet_Change(ByVal Target As Range)

     With Target.CurrentRegion

         .EntireColumn.ColumnWidth = 255

         .EntireColumn.AutoFit

         .EntireRow.AutoFit

     End With

 End Sub

Ensuite, il va simplement suffit de remplacer le Target.CurrentRegion présent juste après le With par le nom de la plage des cellules :

 Private Sub Worksheet_Change(ByVal Target As Range)

     With [_baseArticles]

         .EntireColumn.ColumnWidth = 255

         .EntireColumn.AutoFit

         .EntireRow.AutoFit

     End With

 End Sub 

Cette dernière version de la macro-commande aura un intérêt si la valeur des cellules de la plage se recalcul automatiquement en fonction du résultat d’une autre cellule, qui n’en fait pas partie (je vous invite à consulter la seconde vidéo de cet article pour un exemple).

 

Article initialement publié le 7 février 2019, puis mis à jour le 16 mai 2019 (ajout de la seconde vidéo).



Articles qui pourraient vous intéresser

Les 3 types de menus déroulants d’Excel

Comment regrouper des dates par périodes dans un tableau croisé dynamique sur Excel

Comment filtrer des données en VBA sur Excel

Comment afficher plusieurs onglets d'un même classeur grâce au multi-fenêtrage d'Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.