In precedenza in Podcast 2093, ho mostrato un semplice ordinamento VBA che funziona se non stai ordinando per colore. Oggi, Neeta chiede a VBA di ordinare i dati di Excel in base al colore.
La cosa più complicata dell'ordinamento per VBA è capire quali codici colore RGB stai utilizzando. Nel 99% dei casi, non hai scelto un colore immettendo i valori RGB. Hai scelto un colore utilizzando questo menu a discesa in Excel.
E mentre puoi usare Riempimento, Più colori, Personalizzato per imparare che il colore selezionato è RGB (112,48,160), è una seccatura se hai molti colori.
Quindi, preferisco accendere il registratore di macro e lasciare che il registratore di macro calcoli il codice. Il codice generato dal registratore di macro non è mai perfetto. Ecco il video che mostra come utilizzare il registratore di macro durante l'ordinamento per colore.
Trascrizione del video
Impara Excel da Podcast, episodio 2186: VBA Ordina per colore.
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi, inviata su YouTube. Avevo un video là fuori su come ordinare con VBA e volevano ordinare per colore con VBA, che è molto più complicato. Ho detto: "Perché non accendi il registratore di macro e vedi cosa succede?" E, sfortunatamente, il registratore di macro, sai, ci avvicina ma non ci porta fino in fondo.
Quindi Visualizza, Macro, Registra macro, "HowToSortByColor", Memorizza macro in questa cartella di lavoro - perfetto. Fare clic su OK. Va bene, quindi ora il registratore di macro è in esecuzione, verremo qui nella scheda Dati e diremo Ordina. Useremo una finestra di dialogo Ordina e la costruiremo, va bene? Quindi, diremo che vogliamo aggiungere un livello, Sort on Cherry, ma non Sort on Cell Values; andremo a Ordina per colore cella - Colore cella è il colore di riempimento lì - e vogliamo mettere il rosso sopra e poi copiare quel livello, e mettere il giallo secondo; e poi aggiungeremo un nuovo livello - andremo alla colonna D, la colonna della data - Ordina per colore cella, prima il rosso, copia quel livello, giallo, e poi qui; e poi, qui a Sambuco, colonna E, ci sono alcuni caratteri blu che non voglio vedere come fossero,quindi lo aggiungeremo come Ordinamento sul colore del carattere con il blu in alto; e poi se tutti questi sono un pareggio per nessun colore, aggiungeremo un livello finale solo sulla colonna A - Valori cella, dal più grande al più piccolo; e fare clic su OK.
Va bene, ora, un paio di cose - non saltare questo passaggio successivo - il tuo file, adesso, ti garantisco che è archiviato come xlsx. Questo è un ottimo momento per fare File, Salva con nome e salvarlo come xlsm o xlsb. Se non lo fai, tutto il tuo lavoro fino a questo punto andrà perso quando salverai questo file. Elimineranno le macro di qualsiasi cosa memorizzata in xlsx. Tutto a posto?
Quindi abbiamo smesso di registrare lì, e poi vogliamo andare a vedere le nostre macro. Quindi, puoi farlo con Visualizza, Macro-- Visualizza, Macro-- e trova la macro che abbiamo appena registrato-- HowToSortByColor-- e fai clic su Modifica. Va bene, quindi ecco la nostra macro, e mentre guardo questa, il problema che abbiamo è che oggi abbiamo 25 righe più un'intestazione. Quindi si scende alla riga 26. E hanno hardcoded che guarderanno sempre alla riga 26.
Ma mentre ci penso, specialmente rispetto al vecchio VBA per l'ordinamento, non dobbiamo specificare l'intero intervallo: solo una cella nella colonna. Quindi ovunque abbiano la colonna C26, la ridurrò per dire semplicemente "Ehi, no, guarda la prima cella di quella colonna". Quindi E2, e poi, qui, A2. Quindi nel mio caso, avevo 1, 2, 3, 4, 5, 6, livelli di ordinamento - 6 cose da cambiare.
E poi questa è la parte che il registratore di macro ottiene davvero, davvero male, è che ordinerà solo alle righe 26 tutto il tempo. Quindi cambierò questo. Dirò: "Guarda, inizia dalla gamma A21 ed estendila a .CurrentRegion". Diamo un'occhiata a Excel e vediamo cosa fa. Quindi, se scegliessi una cella qualsiasi, A1 o qualsiasi altra cosa, e premi Ctrl + *, seleziona la regione corrente. Va bene, facciamolo. Qui, dal centro, Ctrl + * e ciò che fa, si estende in tutte le direzioni fino a quando non raggiunge il bordo del foglio di calcolo, sopra il foglio di calcolo o sul bordo destro dei dati o sul bordo inferiore dei dati . Quindi, pronunciando A1 .CurrentRegion, è come andare su A1 e premere Ctrl + *. Tutto a posto? Quindi, qui devi cambiare quella cosa. Ora tutto il resto nella macro va bene; esso 'tutto funzionerà. Hanno ottenuto SortOnCellColor e SortOnFontColor e xlSortOn. Non devo preoccuparmi di niente di tutto ciò; tutto quello che devo fare è guardare qui e vedere che hanno hard-coded la regione che avrebbero usato per l'intervallo, hard-coded fino a che punto sono andati, e non ha bisogno di essere hard-coded. E con quel semplice passaggio, cambiando quei sei elementi e il settimo elemento, abbiamo qualcosa che dovrebbe funzionare.
Ora, facciamo il test. Torniamo qui a Excel e aggiungeremo alcune nuove righe in fondo. Inserirò solo 11 e aggiungeremo un paio di rossi: un rosso, un giallo e poi qui un blu. Tutto a posto. Quindi, se eseguiamo questo codice, esegui questo codice, quindi faccio clic qui e faccio clic sul pulsante Esegui e poi torni indietro, dovremmo vedere che 11 è diventato l'elemento in alto in rosso, è mostrato lì nel gialli, e si mostra nel blues, quindi funziona tutto perfettamente. Perché è andato in cima? Perché è successo che l'ultimo ordinamento è la colonna A e quindi quando c'è un pareggio, guarda alla colonna A come il tie-break. Quindi quel codice funziona.
Per imparare a scrivere VBA, insieme a Tracy Syrstad, ho scritto una serie di libri, Excel VBA e MACRO. C'è stata un'edizione ora per il 2003, 2007, 2010, 2013 e 2016; presto 2019. Va bene, quindi, trova la versione che corrisponde alla tua versione di Excel e questo ti farà salire sulla curva di apprendimento.
Conclusione: l'episodio di oggi è come utilizzare VBA per ordinare per colore. Il modo più semplice per farlo, soprattutto perché non sai quali codici RGB sono stati utilizzati per ciascuno dei colori: hai appena scelto il rosso, non sai quale sia il codice RGB e non vuoi andare a guardare su: accendi il registratore di macro utilizzando Visualizza, Macro, Registra nuova macro. Dopo aver terminato l'ordinamento, fai clic su Interrompi registrazione: si trova nell'angolo in basso a sinistra: Alt + F8 per vedere un elenco di macro, oppure Visualizza, Macro, Visualizza macro, la scheda Visualizza, Macro e quindi Visualizza macro, che crea confusione. PSeleziona la macro e fai clic su Modifica, e ogni volta che vedi C2 su alcuni numeri di intervallo, cambialo in modo che punti alla riga 2. E poi, dove specificano l'intervallo da ordinare, Intervallo ("A1"), CurrentRegion, si espanderà. Tutto a posto.
Bene, ehi, voglio ringraziarti per essere passato, ci vediamo la prossima volta per un altro netcast da.
Nel video, ho impostato un ordinamento a sei livelli. La finestra di dialogo finale è mostrata qui:
Il giorno in cui mi è capitato di registrare la macro, avevo 23 righe di dati più un'intestazione. C'erano sette posizioni nella macro che codificavano come hardcoded il numero di righe. Questi devono essere adeguati.
Per ogni livello di ordinamento, c'è un codice come questo:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) È sciocco che il registratore di macro specifichi C2: C24. Devi solo specificare una cella nella colonna, quindi cambia la prima riga sopra in:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _Apporta una modifica simile per ciascuno dei livelli di ordinamento.
Verso la fine della macro registrata, hai il codice registrato per eseguire effettivamente l'ordinamento. Inizia così:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End WithInvece di ordinare solo A1: E24, modifica il codice per iniziare in A1 ed estenderlo alla regione corrente. (La regione corrente è ciò che ottieni se premi Ctrl + * da una cella).
.SetRange Range("A1").CurrentRegionIl codice finale mostrato nel video è:
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 SubNota
È probabile che la tua cartella di lavoro venga salvata con un'estensione XLSX. Fare un Salva con nome per passare a un'estensione XLSM o XLSB. Eventuali macro salvate in XLSX vengono eliminate.
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Una mela al giorno tiene lontano il VBA."
Tom Urtis
