Lorsque les données téléchargées à partir d'une base de données dépassent la plage d'une zone de liste déroulante, les nouveaux éléments ne sont tout simplement pas affichés. Pour contrer cela, la plage qui sous-tend les zones de liste ou de liste déroulante doit être étendue ou réduite pour correspondre aux données. Cet article examine comment procéder automatiquement.
Il est supposé que le lecteur a affiché le ruban Développeur et est familiarisé avec l'éditeur VBA. Si ce n'est pas le cas, veuillez Google "Excel Developer Tab" ou "Excel Code Window".
Une façon professionnelle d'afficher les listes déroulantes consiste à étendre ou à réduire leurs gammes selon les besoins. Par exemple:
La clé d'une plage dynamique consiste à surveiller le nombre de lignes remplies dans la colonne concernée, à l'aide de la fonction =countA. Cette fonction compte les éléments remplis dans une séquence de cellules jusqu'à ce qu'elle atteigne la dernière ; dans le cas du premier diagramme de l'image ci-dessus, ce serait la ligne 11.
Pour gérer automatiquement une plage, des noms définis doivent suivre le nombre de lignes remplies. Par exemple, nous utilisons eCol (colonne de fin) et eRow (ligne de fin) pour définir les limites de notre plage. Plus nous remplissons de lignes, plus la valeur d'eRow augmente.
Les noms définis ci-dessus ont défini la liste sur une colonne de large (eCol = 1) par le nombre de lignes remplies dans eCol (eRow = 11)
Enfin, les titres apparaissent dans la plage "A2: A" & eRow. Vous remarquerez que la fonction Index est utilisée pour établir la dernière cellule de la plage appelée "Titres". En effet, la plage "A2:A" & eRow se traduit par "A2:A11" à ce stade.
Nous pouvons configurer automatiquement la plage dynamique à l'ouverture du classeur, en utilisant la sous-procédure Auto_open, qui s'exécute avant que le classeur ne devienne visible.
Le code
Ouvrez un classeur et remplissez-le avec une zone de liste déroulante et des données. L'exemple de classeur utilisé dans cet exercice se trouve ici.
Ouvrez la fenêtre du code VBA et insérez un module. Copiez le code ci-dessous dans le module.
L'événement Auto_Open définit les valeurs de la dernière ligne et de la dernière colonne pour la plage dynamique "Titres" et note les modifications ultérieures apportées.
Sub auto_open()
Dim eRow As Integer, eCol As Integer, i As Long
On Error Resume Next
'Clear the present define names, to avoid any duplications
activeworkbook.Names("eCol").Delete
activeworkbook.Names("eRow").Delete
activeworkbook.Names("Titles").Delete
Range("A1").Select
'Titles will appear in the first column, A in this case
eCol = 1
'Find the last populated row
eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
'Define the names
activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub
Sub DropDown1_Change()
MsgBox "Directed by " & Cells(2, 5)
End Sub
Remarque : Pour plus de clarté, toutes les données sont présentées sur une seule page. En général, les valeurs des colonnes A et B, ainsi que D et E, se trouvent sur une autre feuille, éventuellement masquée. Veuillez noter également que la colonne B n’est pas utilisée dans la définition de la plage pour cet exercice ; ses valeurs sont obtenues de manière classique grâce à la propriété Lien de cellule de la zone de liste déroulante (D2 correspond à la sélection du troisième élément de la plage, qui commence en A2, combinée à la fonction INDEX en E3 pour trouver le directeur : =INDEX(B:B;D2+1;1)).
Enregistrez le classeur, puis rouvrez-le. La zone de liste déroulante sera initialement remplie par Auto_open. Ajoutez des éléments aux colonnes A et B et observez les modifications apportées à la zone de liste déroulante.
Récupérer des fichiers Excel endommagés
De temps en temps, les fichiers Excel peuvent être corrompus après un plantage inattendu d'Excel. Si vous avez une sauvegarde, vous pouvez simplement restaurer les données avec votre sauvegarde. Sinon, vous devrez peut-être faire appel à un expert professionnel ou à un outil pour récupérer le Excel corrompu fichiers.
Introduction de l'auteur:
Felix Hooker est un expert en récupération de données dans DataNumen, Inc., qui est le leader mondial des technologies de récupération de données, y compris réparation rar et produits logiciels de récupération sql. Pour plus d'informations, visitez www.datanumen.com