Excel Sorter efter farve med VBA - Excel-tip

Indholdsfortegnelse

Tidligere i Podcast 2093 viste jeg en simpel VBA-sortering, der fungerer, hvis du ikke sorterer efter farve. I dag beder Neeta om, at VBA sorterer Excel-data efter farve.

Den sværeste ting ved at sortere efter VBA er at finde ud af, hvilke RGB-farvekoder du bruger. I 99% af tilfældene valgte du ikke en farve ved at indtaste RGB-værdier. Du valgte en farve ved at bruge denne rullemenu i Excel.

De fleste vælger udfyldnings- eller skrifttypefarve ved hjælp af denne rullemenu

Og mens du kunne bruge Udfyld, Flere farver, Brugerdefineret for at lære, at den valgte farve er RGB (112,48,160), er det en besvær, hvis du har mange farver.

RGB-koder er skjult i denne dialog

Så - jeg foretrækker at tænde for makrooptageren og lade makrooptageren finde ud af koden. Koden genereret af makrooptageren er aldrig perfekt. Her er videoen, der viser, hvordan man bruger makrooptageren, når man sorterer efter farve.

Videoudskrift

Lær Excel fra Podcast, afsnit 2186: VBA Sorter efter farve.

Hej, velkommen tilbage til netcast, jeg er Bill Jelen. Dagens spørgsmål, sendt ind på YouTube. Jeg havde en video derude om, hvordan man sorterede med VBA, og de ville sortere efter farve med VBA, hvilket er meget mere kompliceret. Jeg sagde: "Hvorfor tænder du ikke bare for makrooptageren og ser hvad der sker?" Og desværre, makrooptageren, du ved, det kommer os tæt, men det kommer os ikke helt derhen.

Så vis, makroer, optag makro, "HowToSortByColor", gem makro i denne projektmappe - perfekt. Klik på OK. Okay, så nu kører makrooptageren, vi kommer her til fanen Data, og vi siger Sort. Vi skal bruge en sorteringsdialogboks, og vi skal bygge dette, okay? Så vi vil sige, at vi vil tilføje et niveau, Sorter på kirsebær, men ikke Sorter på celleværdier; vi skal sortere på cellefarve - cellefarve er fyldfarven der - og vi vil lægge rødt ovenpå og derefter kopiere det niveau og sætte gul på anden; og så tilføjer vi et nyt niveau - vi går til kolonne D, datakolonnen - Sorter efter cellefarve, rød først, kopier det niveau, gul og derefter herover; og så, her i Elderberry, kolonne E, er der et par blå skrifttyper, jeg ikke vil se, hvordan det så ud,så vi tilføjer det som en Sort på Font farve med blå på toppen; og så hvis alle disse er uafgjort uden farver overhovedet, tilføjer vi et sidste niveau lige i kolonne A - Celleværdier, største til mindste; og klik på OK.

Okay, nu, et par ting-- spring ikke over dette næste trin-- din fil, lige nu, jeg garanterer, at du er gemt som xlsx. Dette er et godt tidspunkt at lave File, Save As og gemme det som xlsm eller xlsb. Hvis du ikke gør det, går alt dit arbejde til dette punkt tabt, når du gemmer denne fil. De sletter makroerne for alt, hvad der er gemt i xlsx. I orden?

Så vi stoppede optagelsen der, og så vil vi se på vores makroer. Så du kan gøre dette med Vis, Makroer - Vis, Makroer - og find den makro, vi lige har optaget - HowToSortByColor-- og klik på Rediger. Okay, så her er vores makro, og som jeg ser på dette, er det problem, vi har, i dag har vi tilfældigvis 25 rækker plus en overskrift. Så det går ned til række 26. Og de har hårdkodet, at de altid vil se ned til række 26.

Men da jeg tænker på dette, især sammenlignet med den gamle VBA til sortering, behøver vi ikke specificere hele området - kun en celle i kolonnen. Så hvor som helst hvor de har kolonne C26, vil jeg reducere det til bare at sige "Hej, nej, se på den første celle i den kolonne." Så E2, og derefter, her, A2. Så i mit tilfælde havde jeg 1, 2, 3, 4, 5, 6 sorteringsniveauer - 6 ting at ændre.

Og så er dette den del, som makrooptageren bliver rigtig, virkelig dårlig, hvis de kun skal sortere til række 26 hele tiden. Så jeg vil ændre dette. Jeg vil sige, "Se, start i rækkevidde A21 og udvid det til .CurrentRegion." Lad os se på Excel og se, hvad det gør. Så hvis jeg bare ville vælge en celle - A1 eller noget andet - og trykke på Ctrl + *, vælger den den aktuelle region. Okay, lad os gøre det. Her, fra midten, Ctrl + *, og hvad det gør, strækker det sig i alle retninger, indtil det rammer kanten af regnearket, oven på regnearket eller ved den højre kant af dataene eller den nederste kant af dataene . Så ved at sige A1 .CurrentRegion er det som at gå til A1 og trykke på Ctrl + *. I orden? Så her skal du ændre den ting. Nu er alt andet i makroen i orden; det's alle går på arbejde. De fik SortOnCellColor og SortOnFontColor og xlSortOn. Jeg behøver ikke bekymre mig om noget af det; alt hvad jeg skal gøre er at kigge ind her og se, at de hårdkodede den region, de skulle bruge til området, hårdkodede, hvor langt de gik, og det behøver ikke at være hårdkodet. Og med det enkle trin, ændring af disse seks og det syvende element, har vi noget, der skal fungere.

Lad os nu tage testen. Lad os komme tilbage her til Excel, og vi tilføjer nogle nye rækker i bunden. Jeg lægger bare 11'ere der, og vi tilføjer et par røde - en rød, en gul og derefter herover en blå. I orden. Så hvis vi kører denne kode - kører denne kode, så jeg klikker ind her og klikker på knappen Kør - og så kommer tilbage, skal vi se, at 11 blev det øverste element i rødt, det dukkede op der i gule, og det dukker op i bluesen, så det hele fungerer perfekt. Hvorfor gik det til toppen? Fordi det skete, at den sidste sortering er kolonne A, og når der er uafgjort, ser det ud til kolonne A som tiebreaker. Så den kode fungerer.

For at lære at skrive VBA har jeg sammen med Tracy Syrstad skrevet en række bøger, Excel VBA og MACROS. Der har været en udgave nu for 2003, 2007, 2010, 2013 og 2016; snart 2019. Okay, så find den version, der matcher din version af Excel, og dette får dig op i indlæringskurven.

Afslutning: Dagens episode er, hvordan man bruger VBA til at sortere efter farve. Den nemmeste måde at gøre dette på, især da du ikke ved, hvilke RGB-koder der blev brugt til hver af farverne - du valgte bare rødt, du ved ikke, hvad RGB-koden er, og du vil ikke se ud det op - tænd for makrooptageren ved hjælp af Vis, Makroer, Optag ny makro. Når du er færdig med at sortere, skal du klikke på Stop optagelse - det er i nederste venstre hjørne - Alt + F8 for at se en liste over makroer eller Vis, Makroer, Vis makro - fanen Vis, Makroer og derefter Vis makroer - det er forvirrende. PSælg din makro, og klik på Rediger, og når som helst du ser C2 til nogle rækkenumre, skal du bare ændre den til punkt til række 2. Og hvor de angiver det område, der skal sorteres, udvides området ("A1"), CurrentRegion. I orden.

Nå, hej, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra.

I videoen oprettede jeg en sortering på seks niveauer. Afslutningsdialogboksen vises her:

Sorter efter rød, gul i C, rød, gul i d, blå i e, tal i a

Den dag, hvor jeg tilfældigvis optagede makroen, havde jeg 23 rækker med data plus en overskrift. Der var syv steder i makroen, der hårdkodede antallet af rækker. Disse skal justeres.

For hvert sorteringsniveau er der kode som denne:

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0)

Dette er fjollet, at makrooptageren specificerer C2: C24. Du skal kun angive en celle i kolonnen, så skift den første linje ovenfor til:

ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _

Foretag en lignende ændring for hvert af sorteringsniveauerne.

I slutningen af den optagede makro har du den registrerede kode til faktisk at udføre sorteringen. Det starter sådan her:

With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With

I stedet for kun at sortere A1: E24, skal du ændre koden for at starte i A1 og udvide til den aktuelle region. (Nuværende region er hvad du får, hvis du trykker på Ctrl + * fra en celle).

.SetRange Range("A1").CurrentRegion

Den endelige kode vist i videoen er:

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 Sub

Bemærk

Det er sandsynligt, at din projektmappe er gemt med en XLSX-udvidelse. Lav en Save As for at skifte til en XLSM- eller XLSB-udvidelse. Alle makroer, der er gemt i XLSX, slettes.

Excel-tanken om dagen

Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over: