Trouver toutes les cellules qui ont été remplis avec n'importe quelle couleur et en mettant en évidence correspondant en-têtes de colonne dans excel vba
Mon problème:
J'ai fait un grand (2 000) macro qui s'exécute sur notre société modèle et résout certains problèmes courants et met en lumière d'autres enjeux que nous avons préalablement à l'importation. Le fichier de modèle a toujours 150 colonnes et est dans la plupart des cas, plus de 15 000 lignes (parfois même de plus de 30 000). La macro fonctionne bien, en mettant en évidence toutes les cellules qui contiennent des erreurs en fonction de nos règles de données, mais avec un fichier avec autant de colonnes et de lignes j'ai pensé qu'il serait pratique d'ajouter un extrait de ma macro qui l'obligerait à trouver toutes les cellules qui ont été soulignées et ensuite mettre en évidence les en-têtes de colonnes des colonnes qui contiennent les cellules mises en surbrillance.
Méthodes que j'ai trouvé lors de la recherche d'une solution:
- SpecialCells
xlCellTypeAllFormatConditions
ne fonctionne que pour la mise en forme conditionnelle, de sorte que n'est pas plausible méthode pour ma situation - Rick Rothstein est UDF de ici
Sub FindYellowCells() Dim YellowCell As Range, FirstAddress As String Const IndicatorColumn As String = "AK" Columns(IndicatorColumn).ClearContents ' The next code line sets the search for Yellow color... the next line after it (commented out) searches ' for the ColorIndex 6 (which is usually yellow), so use whichever code line is applicable to your situation Application.FindFormat.Interior.Color = vbYellow 'Application.FindFormat.Interior.ColorIndex = 6 Set YellowCell = Cells.Find("*", After:=Cells(Rows.Count, Columns.Count), SearchFormat:=True) If Not YellowCell Is Nothing Then FirstAddress = YellowCell.Address Do Cells(YellowCell.Row, IndicatorColumn).Value = "X" Set YellowCell = Cells.Find("*", After:=YellowCell, SearchFormat:=True) If YellowCell Is Nothing Then Exit Do Loop While FirstAddress <> YellowCell.Address End If End Sub
Ce serait parfait avec un peu de bricolage, à l'exception des fichiers peut avoir plusieurs colorfills. Puisque notre modèle est tellement grand que j'ai appris qu'il faut un certain temps pour exécuter une seule instance de
Find
pour trouver une seule colorfill dans leUsedRange
. - À l'aide de filtrage, peut-être à vélo à travers toutes les colonnes et de vérifier s'ils contiennent des cellules qui a tout colorfill. Serait-ce tout plus vite si?
Donc, ma question:
- Comment pourrais-je accomplir trouver toutes les colonnes qui contiennent des tout colorfilled cellules? Plus précisément, ce qui serait le plus efficace (plus rapide) façon d'atteindre cet objectif?
C'est vrai, mais la seule raison pour laquelle je n'ai pas tourné à qui pourtant est parce qu'il exigerait d'édition sur 15 sous-routines et de trouver chaque cas où une cellule serait r en surbrillance. Donc, oui c'est une option/une solution, je suis juste essayer pour voir si il y avait un moyen plus rapide avant de me tourner vers ça.
Vous pouvez créer un seul sous pour faire l'enregistrement (peut-être à une chaîne de caractères dans le nom de la collection) et juste à ajouter des appels à des sous à des endroits qui peuvent être trouvés facilement par une recherche
Pourriez-vous me donner un exemple? Je veux m'assurer que je suis vous suit correctement.
OriginalL'auteur CaffeinatedCoder | 2016-03-13
Vous devez vous connecter pour publier un commentaire.
Les plus performantes solution serait à la recherche en utilisant la récursivité par demi-intervalle.
Il faut moins de 5 secondes pour tag les colonnes d'une feuille de calcul avec 150 colonnes et de 30000 lignes.
Le code à la recherche d'une couleur spécifique:
Et à la recherche de n'importe quelle couleur:
vbNull
? Deuxièmement, est-ce votre dernier commentaire précis? Je pense que si c'étaitvbNull
qu'il n'y avait pas de couleurs dans cette gamme. Peut-être que vous pourriez essayer de préciser que jusqu' (expliquer) pour moi?Juste un commentaire pour vous assurer que vous avez vu mon premier depuis que je sais que vous avez été d'édition alors que j'ai posté
J'ai mis à jour la réponse à la recherche de n'importe quelle couleur. Et oui c'est exact, la colorIndex est Null si il n'y a plus de 1 couleur et -4142 pour la couleur par défaut.
Merci pour la mise à jour de la solution et une explication! Je vais tester ce code demain matin et marquer ce que la réponse une fois que je le vois de travail.
oui la .L'intérieur.ColorIndex est mis à jour lorsque le .L'intérieur.La couleur est assignée soit manuellement, par un format de condition, ou par code.
OriginalL'auteur Florent B.
Avant:
L'exécution de cette macro court:
produit:
Je ne sais pas à propos de la question de vitesse. Si la couleur des cellules sont près du sommet de la colonne, le code sera exécuté super rapide; si la couleur des cellules sont manquants ou près du bas de la colonne, pas tellement.
EDIT#1:
Veuillez noter que mon code ne sera pas trouver des cellules de couleur conditionnelle.
If r.DisplayFormat.Interior.ColorIndex <> xlNone Then
Malheureusement, je suis toujours à l'aide de Excel 2007/Windows7
J'ai mis la restriction de EDIT#1
C'est une solution, mais je crois qu'il serait assez lent pour ma situation, comme le souligne peuvent être dispersés en haut, au milieu, et/ou en bas. Aussi, @Jeeped je suis seulement à la recherche de trouver les couleurs qui ne sont pas comblés à partir de la mise en forme conditionnelle.
Dès que mon code est la première couleur de la cellule dans une colonne, les couleurs de l'en-tête et de passer à la colonne suivante.
OriginalL'auteur Gary's Student
La Gamme.La valeur de la propriété a en fait trois en option xlRangeValueDataType paramètres. La valeur par défaut est xlRangeValueDefault et c'est tout (par omission) plus jamais quelqu'un utilise.
La xlRangeValueXMLSpreadsheet option permet de récupérer un XML bloc de données qui décrit la plupart des propriétés de la cellule maintient. Une cellule sans Gamme.Intérieur bien au-delà de xlAutomatic sont les suivantes élément XML,
... alors qu'une cellule avec un .L'intérieur.Couleur propriété aura la suite de l'élément XML,
Il est bien établi que le dumping d'une feuille de calcul de valeurs dans un tableau de type variant et de traitement in-memory est nettement plus rapide qu'en parcourant les cellules afin de récupérer le
.Value(xlRangeValueXMLSpreadsheet)
et de l'exécution d'une Fonction InStr sur le blob de données XML devrait être beaucoup plus rapide.J'ai couru ce contre 30K lignes de 26 colonnes. Alors que chaque colonne a été examiné, je ne les avais semées chaque troisième colonne .L'intérieur.Propriété de couleur à un endroit aléatoire dans les 30K lignes. Il a fallu environ une minute et demie.
Chaque colonne de 30K lignes produites XML enregistrement qui a été presque 3Mbs taille; une longueur de 2,970,862 était typique. Une fois lu dans une variable, il a été fouillé pour les empreintes digitales d'un ensemble de l'intérieur de remplissage.
À jeter le lire dans la chaîne de caractères de type var et la réalisation de l'InStr directement sur le .Valeur(xlRangeValueXMLSpreadsheet) améliore le temps d'environ deux secondes.
Range.Value
propriété a un paramètre facultatif. Cela prend un peu plus longtemps que je le voudrais, si 🙁C'était un exercice intellectuel. Ce que vous avez besoin est un "helper" sous qui vous permettra d'obtenir de la cellule de feuille de calcul et la couleur est passé à chaque fois que votre 15 autres routines voulez à la couleur d'une cellule. Le "helper" journal de la cellule, de créer un lien hypertexte vers la cellule de la feuille de calcul et la couleur. Plus tard, quand vous voulez corriger les erreurs, vous pouvez simplement cliquer sur le journal de lien hypertexte et aller directement à le voyou de la valeur.
OriginalL'auteur
Ma proposition à l'aide d'
AutoFilter
méthode deRange
objetil fonctionne très rapide
OriginalL'auteur user3598756