Tidligere i Podcast 2093 viste jeg en enkel VBA-sortering som fungerer hvis du ikke sorterer etter farge. I dag ber Neeta om at VBA skal sortere Excel-data etter farge.
Det vanskeligste med å sortere etter VBA er å finne ut hvilke RGB-fargekoder du bruker. I 99% av tilfellene valgte du ikke en farge ved å skrive inn RGB-verdier. Du valgte en farge ved å bruke denne rullegardinmenyen i Excel.
Og mens du kan bruke Fill, More Colors, Custom for å lære at den valgte fargen er RGB (112,48,160), er det et problem hvis du har mange farger.
Så - jeg foretrekker å slå på makroopptakeren og la makroopptakeren finne ut koden. Koden generert av makroopptakeren er aldri perfekt. Her er videoen som viser hvordan du bruker makroopptakeren når du sorterer etter farger.
Videoutskrift
Lær Excel fra Podcast, episode 2186: VBA Sorter etter farge.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål, sendt inn på YouTube. Jeg hadde en video der ute om hvordan jeg kunne sortere med VBA, og de ønsket å sortere etter farge med VBA, noe som er mye mer komplisert. Jeg sa: "Hvorfor slår du ikke bare på makroopptakeren og ser hva som skjer?" Og dessverre makroopptakeren, du vet, den kommer oss nær, men den kommer oss ikke helt dit.
Så se, makroer, ta opp makro, "HowToSortByColor", lagre makro i denne arbeidsboken - perfekt. Klikk OK. Ok, så nå går makroopptakeren, vi kommer hit til Data-fanen, og vi skal si Sorter. Vi skal bruke en sorteringsdialogboks, og vi skal bygge denne, ok? Så vi skal si at vi vil legge til et nivå, Sorter på kirsebær, men ikke Sorter på celleverdier; vi skal sortere på cellefarge - cellefarge er fyllfargen der - og vi vil sette rødt på toppen og deretter kopiere det nivået, og sette gult sekund; og så legger vi til et nytt nivå - vi går til kolonne D, datakolonnen - Sorter på cellefarge, rød først, kopier det nivået, gult og så herover; en så, her i Elderberry, kolonne E, er det noen få blå skrifter jeg ikke vil se hvordan det så ut,så vi vil legge til det som en Sort on Font-farge med blått på toppen; og så hvis alle disse er uavgjort uten farger i det hele tatt, legger vi til et endelig nivå bare i kolonne A - Celleverdier, største til minste; og klikk OK.
OK, nå, et par ting-- ikke hopp over dette neste trinnet - filen din, akkurat nå, jeg garanterer at du er lagret som xlsx. Dette er et flott tidspunkt å gjøre File, Save As, og lagre det som xlsm eller xlsb. Hvis du ikke gjør det, vil alt arbeidet ditt til dette tidspunktet gå tapt når du lagrer denne filen. De vil slette makroene til alt som er lagret i xlsx. Ok?
Så vi sluttet å ta opp der, og så vil vi se på makroene våre. Så du kan gjøre dette med View, Macros-- View, Macros-- og finne makroen vi nettopp har spilt inn - HowToSortByColor-- og klikk på Rediger. OK, så her er makroen vår, og når jeg ser på dette, er problemet vi har, i dag har vi tilfeldigvis 25 rader pluss en overskrift. Så det går ned til rad 26. Og de har hardkodet at de alltid kommer til å se ned til rad 26.
Men når jeg tenker på dette, spesielt i forhold til den gamle VBA for sortering, trenger vi ikke spesifisere hele området - bare en celle i kolonnen. Så hvor som helst der de har kolonne C26, skal jeg redusere den til bare å si "Hei, nei, se på den første cellen i den kolonnen." Så E2, og så, her, A2. Så i mitt tilfelle hadde jeg 1, 2, 3, 4, 5, 6, sorteringsnivåer - 6 ting å endre.
Og så er dette delen som makroopptakeren blir veldig, veldig dårlig, er at de bare skal sortere til rad 26 hele tiden. Så jeg kommer til å endre dette. Jeg skal si, "Se, start på rekkevidde A21, og utvid den til .CurrentRegion." La oss ta en titt på Excel og se hva det gjør. Så hvis jeg bare ville velge en celle - A1 eller noe annet - og trykke Ctrl + *, velger den gjeldende region. Ok, la oss gjøre det. Her, fra midten, Ctrl + *, og hva det gjør, er at den strekker seg i alle retninger til den treffer kanten av regnearket, på toppen av regnearket, eller i høyre kant av dataene eller underkanten av dataene . Så, ved å si A1 .CurrentRegion, er det som å gå til A1 og trykke Ctrl + *. Ok? Så her må du endre den tingen. Nå er alt annet i makroen greit; den'alle skal på jobb. De fikk SortOnCellColor og SortOnFontColor og xlSortOn. Jeg trenger ikke å bekymre meg for noe av det; Alt jeg trenger å gjøre er å se inn her og se at de har hardkodet regionen de skulle bruke for området, hardkodet hvor langt de gikk, og det trenger ikke å være hardkodet. Og med det enkle trinnet, å endre disse seks elementene og det syvende elementet, har vi noe som skal fungere.
La oss nå gjøre testen. La oss komme tilbake hit til Excel, og vi vil legge til noen nye rader nederst. Jeg skal bare sette 11'ere der, og vi vil legge til et par røde - en rød, en gul og så herover en blå. Ok. Så hvis vi kjører denne koden - kjører denne koden, så jeg klikker inn her og klikker på Kjør-knappen - og så kommer tilbake, skal vi se at den 11 ble toppgjenstanden i rødt, den dukket opp der i gule, og det dukker opp i bluesen, så alt fungerer perfekt. Hvorfor gikk det til topps? Fordi det skjedde at den siste sorteringen er kolonne A, og når det er uavgjort, ser det ut til kolonne A som tiebreaker. Så den koden fungerer.
For å lære å skrive VBA har jeg sammen med Tracy Syrstad skrevet en serie bøker, Excel VBA og MACROS. Det har vært en utgave nå for 2003, 2007, 2010, 2013 og 2016; snart 2019. Greit, så, finn den versjonen som samsvarer med din versjon av Excel, så får du opp læringskurven.
Avslutning: Dagens episode er hvordan du bruker VBA til å sortere etter farge. Den enkleste måten å gjøre dette på, spesielt siden du ikke vet hvilke RGB-koder som ble brukt for hver av fargene - du valgte bare rødt, du vet ikke hva RGB-koden er, og du vil ikke se det opp - slå på makroopptakeren ved hjelp av View, Macros, Record New Macro. Når du er ferdig med sorteringen, klikker du Stopp opptak - den er nederst til venstre - Alt + F8 for å se en liste over makroer, eller Vis, Makroer, Vis makro - Vis-fanen, Makroer og deretter Vis makroer - det er forvirrende. PSelektro makro og klikk på Rediger, og når som helst du ser C2 til noen områdenumre, endrer du den til å peke til rad 2. Og der, hvor de spesifiserer området som skal sorteres, utvides området ("A1"), CurrentRegion. Ok.
Vel, hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra.
I videoen satte jeg opp en seks-nivå sortering. Sluttdialogboksen vises her:
Den dagen jeg tilfeldigvis tok opp makroen, hadde jeg 23 rader med data pluss en overskrift. Det var syv steder i makroen som hardkodet antall rader. Disse må justeres.
For hvert sorteringsnivå er det kode som denne:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) Dette er dumt at makroopptakeren spesifiserer C2: C24. Du trenger bare å spesifisere en celle i kolonnen, så endre den første linjen over til:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Gjør en lignende endring for hvert av sorteringsnivåene.
Nær slutten av den innspilte makroen har du den innspilte koden til å faktisk gjøre sorteringen. Det starter slik:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithI stedet for bare å sortere A1: E24, kan du endre koden for å starte i A1 og utvide til gjeldende region. (Nåværende region er hva du får hvis du trykker Ctrl + * fra en celle).
.SetRange Range("A1").CurrentRegionDen endelige koden som vises 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 SubMerk
Det er sannsynlig at arbeidsboken din er lagret med en XLSX-utvidelse. Lagre som for å bytte til en XLSM- eller XLSB-utvidelse. Eventuelle makroer som er lagret i XLSX slettes.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Et Apple om dagen holder VBA borte."
Tom Urtis
