Eerder in Podcast 2093 liet ik een eenvoudige VBA-sortering zien die werkt als je niet op kleur sorteert. Tegenwoordig vraagt Neeta aan de VBA om Excel-gegevens op kleur te sorteren.
Het lastigste aan sorteren op VBA is uitzoeken welke RGB-kleurcodes u gebruikt. In 99% van de gevallen koos u geen kleur door RGB-waarden in te voeren. U koos een kleur met behulp van deze vervolgkeuzelijst in Excel.
En hoewel je Fill, More Colors, Custom zou kunnen gebruiken om erachter te komen dat de geselecteerde kleur RGB is (112,48,160), is dat een gedoe als je veel kleuren hebt.
Dus - ik geef er de voorkeur aan om de macrorecorder aan te zetten en de macrorecorder de code te laten uitzoeken. De code die door de macrorecorder wordt gegenereerd, is nooit perfect. Hier is de video die laat zien hoe u de macrorecorder gebruikt bij het sorteren op kleur.
Videotranscriptie
Leer Excel van Podcast, aflevering 2186: VBA Sorteren op kleur.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. De vraag van vandaag, ingezonden op YouTube. Ik had een video over hoe te sorteren met VBA, en ze wilden op kleur sorteren met VBA, wat veel gecompliceerder is. Ik zei: "Waarom zet je niet gewoon de macrorecorder aan en kijk je wat er gebeurt?" En, helaas, de macrorecorder, weet je, het brengt ons dichtbij, maar het brengt ons niet helemaal daar.
Dus bekijken, macro's, macro opnemen, "HowToSortByColor", macro opslaan in deze werkmap - perfect. Klik OK. Oké, dus nu de macrorecorder draait, komen we hier naar het tabblad Gegevens en gaan we Sorteren zeggen. We gaan een dialoogvenster Sorteren gebruiken en we gaan dit bouwen, oké? We gaan dus zeggen dat we een niveau willen toevoegen, Sorteren op Cherry, maar niet Sorteren op celwaarden; we gaan sorteren op celkleur - celkleur is daar de vulkleur - en we willen rood bovenaan plaatsen en dan dat niveau kopiëren, en geel als tweede plaatsen; en dan voegen we een nieuw niveau toe - we gaan naar kolom D, de datumkolom - Sorteer op celkleur, eerst rood, kopieer dat niveau, geel en dan hier; en dan, hier in Vlierbes, kolom E, is er een paar blauwe lettertypen. Ik wil niet zien hoe dat eruit zag,dus we voegen dat toe als een Sorteer op lettertypekleur met blauw bovenaan; en als deze allemaal een gelijkspel zijn voor helemaal geen kleuren, voegen we een laatste niveau toe aan kolom A - Celwaarden, grootste naar kleinste; en klik op OK.
Oké, een paar dingen - sla deze volgende stap niet over - je bestand, op dit moment garandeer ik dat je wordt opgeslagen als xlsx. Dit is een goed moment om Bestand, Opslaan als te doen en het op te slaan als xlsm of xlsb. Als u dat niet doet, gaat al uw werk tot nu toe verloren als u dit bestand opslaat. Ze zullen de macro's verwijderen van alles dat is opgeslagen in xlsx. Oké?
Dus zijn we daar gestopt met opnemen, en dan willen we onze macro's gaan bekijken. U kunt dit dus doen met View, Macro's - View, Macros - en zoek de macro die we zojuist hebben opgenomen - HowToSortByColor - en klik op Bewerken. Oké, dus hier is onze macro, en als ik dit bekijk, is het probleem dat we vandaag 25 rijen plus een kop hebben. Dus het gaat naar rij 26. En ze hebben hard gecodeerd dat ze altijd naar rij 26 zullen kijken.
Maar terwijl ik hierover nadenk, vooral in vergelijking met de oude VBA voor sorteren, hoeven we niet het hele bereik op te geven - slechts één cel in de kolom. Dus overal waar ze kolom C26 hebben, ga ik het verkleinen en zeggen: "Hé, nee, kijk naar de eerste cel in die kolom." Dus E2, en dan, hier, A2. Dus in mijn geval had ik 1, 2, 3, 4, 5, 6, sorteerniveaus - 6 dingen om te veranderen.
En dit is het deel dat de macrorecorder echt heel erg krijgt, namelijk dat ze alleen maar naar rij 26 gaan sorteren. Dus ik ga dit veranderen. Ik ga zeggen: "Kijk, begin bij bereik A21 en breid het uit tot .CurrentRegion." Laten we eens kijken naar Excel en kijken wat dat doet. Dus als ik een cel zou kiezen - A1 of iets anders - en op Ctrl + * zou drukken, selecteert het de huidige regio. Oké, laten we het doen. Hier, vanuit het midden, Ctrl + *, en wat dat doet, is dat het zich in alle richtingen uitstrekt tot het de rand van de spreadsheet raakt, bovenop de spreadsheet, of aan de rechterkant van de gegevens of de onderkant van de gegevens . Dus door A1 .CurrentRegion te zeggen, is het alsof je naar A1 gaat en op Ctrl + * drukt. Oké? Dus hier moet je dat ding veranderen. Nu is al het andere in de macro in orde; het'gaat allemaal aan het werk. Ze hebben de SortOnCellColor en SortOnFontColor en xlSortOn. Ik hoef me daar geen zorgen over te maken; het enige wat ik hoef te doen is hier naar binnen kijken en zien dat ze de regio die ze gingen gebruiken voor het bereik hard gecodeerd hebben, hard gecodeerd hoe ver die gingen, en het hoeft niet hard gecodeerd te zijn. En met die simpele stap, het veranderen van die zes items en het zevende item, hebben we iets dat zou moeten werken.
Laten we nu de test doen. Laten we hier terugkomen naar Excel en we zullen onderaan enkele nieuwe rijen toevoegen. Ik zal daar gewoon 11s plaatsen, en we zullen een paar Reds toevoegen - een rode, een gele en dan hier een blauwe. Oké. Dus als we deze code gaan uitvoeren - voer deze code uit, dus ik klik hier en klik op de knop Uitvoeren - en kom dan terug, we zouden moeten zien dat die 11 het bovenste item in rood werd, het verscheen daar in de geel, en het verschijnt in de blues, dus dat werkt allemaal perfect. Waarom ging het naar de top? Omdat het is gebeurd dat de laatste sortering kolom A is en dus als er een gelijkspel is, lijkt het op kolom A als de tiebreak. Dus die code werkt.
Om VBA te leren schrijven, heb ik, samen met Tracy Syrstad, een serie boeken geschreven, Excel VBA en MACROS. Er is nu een editie voor 2003, 2007, 2010, 2013 en 2016; binnenkort 2019. Oké, dus ga op zoek naar de versie die overeenkomt met uw versie van Excel en dit zal u op weg helpen.
Afronding: de aflevering van vandaag is: VBA gebruiken om op kleur te sorteren. De gemakkelijkste manier om dit te doen, vooral omdat je niet weet welke RGB-codes voor elk van de kleuren zijn gebruikt - je hebt gewoon rood gekozen, je weet niet wat de RGB-code is en je wilt niet gaan kijken Zet de macrorecorder aan met View, Macro's, Record New Macro. Nadat u klaar bent met sorteren, klikt u op Opname stoppen - het staat in de linkerbenedenhoek - Alt + F8 om een lijst met macro's te zien, of Weergave, Macro's, Macro bekijken - het tabblad Weergave, Macro's en dan Bekijk macro's - dat is verwarrend. PSelecteer uw macro en klik op Bewerken, en elke keer dat u C2 ziet voor een aantal bereiknummers, wijzigt u het zodat het naar rij 2 wijst. En dan, waar ze het bereik specificeren dat moet worden gesorteerd, zal Bereik ("A1"), HuidigeRegio, uitbreiden. Oké.
Nou, hey, ik wil je bedanken voor het langskomen, ik zie je de volgende keer voor een nieuwe netcast van.
In de video heb ik een sortering op zes niveaus opgezet. Het einde-dialoogvenster wordt hier weergegeven:
Op de dag dat ik de macro opnam, had ik 23 rijen met gegevens plus een kop. Er waren zeven plaatsen in de macro die het aantal rijen hard codeerden. Deze moeten worden aangepast.
Voor elk sorteerniveau is er een code zoals deze:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Dit is stom dat de macrorecorder C2: C24 specificeert. U hoeft maar één cel in de kolom op te geven, dus verander de eerste regel hierboven in:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Breng een vergelijkbare wijziging aan voor elk van de sorteerniveaus.
Tegen het einde van de opgenomen macro heb je de opgenomen code om daadwerkelijk te sorteren. Het begint als volgt:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithIn plaats van alleen A1: E24 te sorteren, wijzigt u de code zodat deze begint in A1 en uitbreidt tot de huidige regio. (Huidige regio is wat u krijgt als u vanuit een cel op Ctrl + * drukt).
.SetRange Range("A1").CurrentRegionDe laatste code die in de video wordt getoond, is:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End SubOpmerking
Het is waarschijnlijk dat uw werkmap is opgeslagen met een XLSX-extensie. Voer een Opslaan als uit om over te schakelen naar een XLSM- of XLSB-extensie. Alle macro's die in XLSX zijn opgeslagen, worden verwijderd.
Excel-gedachte van de dag
Ik heb mijn Excel Master-vrienden om advies over Excel gevraagd. De gedachte van vandaag om na te denken:
"Een appel per dag houdt de VBA weg."
Tom Urtis
