Excel Sortera efter färg med VBA - Excel Tips

Tidigare i Podcast 2093 visade jag en enkel VBA-sortering som fungerar om du inte sorterar efter färg. Idag ber Neeta att VBA ska sortera Excel-data efter färg.

Det svåraste med att sortera efter VBA är att ta reda på vilka RGB-färgkoder du använder. I 99% av fallen valde du inte en färg genom att ange RGB-värden. Du valde en färg genom att använda den här rullgardinsmenyn i Excel.

De flesta väljer Fyll- eller teckensnittsfärg med den här rullgardinsmenyn

Och medan du kan använda Fyll, Fler färger, Anpassad för att lära dig att den valda färgen är RGB (112,48,160), är det ett besvär om du har många färger.

RGB-koder är dolda i denna dialog

Så - jag föredrar att sätta på makroinspelaren och låta makroinspelaren ta reda på koden. Koden som genereras av makroinspelaren är aldrig perfekt. Här är videon som visar hur man använder makroinspelaren när man sorterar efter färg.

Videoutskrift

Lär dig Excel från Podcast, avsnitt 2186: VBA Sortera efter färg.

Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga, skickad in på YouTube. Jag hade en video där ute om hur man sorterar med VBA, och de ville sortera efter färg med VBA, vilket är mycket mer komplicerat. Jag sa, "Varför slår du inte bara på makroinspelaren och ser vad som händer?" Och tyvärr, makroinspelaren, du vet, det kommer oss nära men det tar oss inte hela vägen dit.

Så se, makron, spela in makro, "HowToSortByColor", lagra makro i den här arbetsboken - perfekt. Klicka på OK. Okej, så nu är makroinspelaren igång, vi kommer hit till fliken Data och vi säger Sortera. Vi ska använda en sorteringsdialogruta och vi kommer att bygga den här, okej? Så vi ska säga att vi vill lägga till en nivå, Sortera på körsbär, men inte Sortera på cellvärden; vi ska sortera på cellfärg - Cellfärg är fyllningsfärgen där-- och vi vill lägga rött ovanpå och sedan kopiera den nivån och sätta gult andra; och sedan lägger vi till en ny nivå - vi går till kolumn D, datumkolumnen - Sortera på cellfärg, röd först, kopiera den nivån, gul och sedan hit; och då, här i Elderberry, kolumn E, finns det några blå teckensnitt som jag inte vill se hur det såg ut,så vi lägger till det som en Sortera på teckensnittsfärg med blått på toppen; och om alla dessa är oavgjort utan färger alls, lägger vi till en slutlig nivå bara i kolumn A - Cellvärden, största till minsta; och klicka på OK.

Okej, nu, ett par saker-- hoppa inte över nästa steg-- din fil, just nu, jag garanterar att du är lagrad som xlsx. Det här är en bra tid att göra File, Save As och spara det som xlsm eller xlsb. Om du inte gör det kommer allt ditt arbete att gå förlorat när du sparar den här filen. De tar bort makron för allt som är lagrat i xlsx. OK?

Så vi slutade spela in där, och sedan vill vi titta på våra makron. Så du kan göra detta med Visa, Makron-- Visa, Makron-- och hitta det makro vi just spelade in - HowToSortByColor-- och klicka på Redigera. Okej, så här är vårt makro, och när jag tittar på detta är problemet vi har idag 25 rader plus en rubrik. Så det går ner till rad 26. Och de har hårdkodat att de alltid kommer att se ner till rad 26.

Men när jag tänker på detta, särskilt jämfört med den gamla VBA för sortering, behöver vi inte ange hela intervallet - bara en cell i kolumnen. Så var som helst där de har kolumn C26 kommer jag att minska det till att bara säga "Hej, nej, titta på den första cellen i den kolumnen." Så E2, och sedan, här, A2. Så i mitt fall hade jag 1, 2, 3, 4, 5, 6, sorteringsnivåer - 6 saker att ändra.

Och då är detta den del som makroinspelaren blir riktigt, riktigt dålig, är att de bara kommer att sortera till rad 26 hela tiden. Så jag ska ändra det här. Jag kommer att säga, "Titta, börja vid räckvidd A21 och utöka det till .CurrentRegion." Låt oss ta en titt på Excel och se vad det gör. Så om jag bara skulle välja någon cell - A1 eller något annat - och trycka på Ctrl + *, väljer den nuvarande regionen. Okej, låt oss göra det. Här, från mitten, sträcker sig Ctrl + *, och vad det gör, att det sträcker sig i alla riktningar tills det träffar kanten på kalkylbladet, ovanpå kalkylarket eller vid den högra kanten av data eller nedre kanten av data . Så genom att säga A1 .CurrentRegion är det som att gå till A1 och trycka på Ctrl + *. OK? Så här måste du ändra den saken. Nu är allt annat i makrot bra; den's alla kommer att arbeta. De fick SortOnCellColor och SortOnFontColor och xlSortOn. Jag behöver inte oroa mig för något av det; allt jag behöver göra är att titta in här och se att de hårdkodade regionen de skulle använda för intervallet, hårdkodade hur långt de gick, och det behöver inte vara hårdkodat. Och med det enkla steget, att ändra dessa sex och det sjunde objektet, har vi något som ska fungera.

Nu ska vi göra testet. Låt oss komma tillbaka hit till Excel och vi lägger till några nya rader längst ner. Jag lägger bara 11-talet där, och vi lägger till ett par röda - en röd, en gul och sedan här en blå. OK. Så om vi kör den här koden - kör den här koden, så jag klickar in här och klickar på Kör-knappen - och kommer sedan tillbaka, vi borde se att den 11 blev toppobjektet i rött, det visade sig där i gula, och det dyker upp i bluesen, så allt fungerar perfekt. Varför gick det till toppen? Eftersom det hände att den sista sorteringen är kolumn A och så när det är slips ser det ut till kolumn A som slipsbrytare. Så den koden fungerar.

För att lära mig att skriva VBA har jag, tillsammans med Tracy Syrstad, skrivit en serie böcker, Excel VBA och MACROS. Det har funnits en utgåva nu för 2003, 2007, 2010, 2013 och 2016; snart 2019. Okej, så leta reda på den version som matchar din version av Excel så får du upp inlärningskurvan.

Avslutning: Dagens avsnitt är hur man använder VBA för att sortera efter färg. Det enklaste sättet att göra detta, särskilt eftersom du inte vet vilka RGB-koder som användes för var och en av färgerna - du valde bara rött, du vet inte vad RGB-koden är och du vill inte titta upp - slå på makroinspelaren med View, Macros, Record New Macro. När du är klar med sorteringen klickar du på Stoppa inspelning - det är i det nedre vänstra hörnet - Alt + F8 för att se en lista med makron, eller Visa, Makron, Visa makro - fliken Visa, makron och sedan Visa makron - det är förvirrande. PSvälj ditt makro och klicka på Redigera, och när som helst du ser C2 till vissa intervallnummer, ändra det bara till punkt 2. Och sedan, där de anger intervallet som ska sorteras, kommer Range ("A1"), CurrentRegion, att expandera. OK.

Tja, hej, jag vill tacka dig för att du stannade, vi ses nästa gång för en ny netcast från.

I videon satte jag upp en sortering på sex nivåer. Slutdialogrutan visas här:

Sortera efter rött, gult i C, rött, gult i d, blått i e, siffror i a

Den dagen jag råkar spela in makrot hade jag 23 rader med data plus en rubrik. Det fanns sju platser i makrot som hårdkodade antalet rader. Dessa måste justeras.

För varje sorteringsnivå finns det kod så här:

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

Det är dumt att makroinspelaren anger C2: C24. Du behöver bara ange en cell i kolumnen, så ändra den första raden ovan till:

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

Gör en liknande ändring för var och en av sorteringsnivåerna.

Nära slutet av det inspelade makrot har du den inspelade koden för att faktiskt göra sorteringen. Det börjar så här:

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

Snarare än att bara sortera A1: E24, ändra koden så att den börjar i A1 och sträcker sig till den aktuella regionen. (Nuvarande region är vad du får om du trycker på Ctrl + * från en cell).

.SetRange Range("A1").CurrentRegion

Den sista koden som visas i videon är:

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

Notera

Det är troligt att din arbetsbok sparas med ett XLSX-tillägg. Gör en Save As för att byta till en XLSM- eller XLSB-förlängning. Alla makron som sparas i XLSX raderas.

Excel-tanke på dagen

Jag har frågat mina Excel Master-vänner om deras råd om Excel. Dagens tanke att fundera på: