Macro VBA di Excel per ordinare i dati. Non mi piace il modo in cui il registratore di macro crea codice extra per l'ordinamento. L'ordinamento in Excel VBA dovrebbe essere semplice. Una riga di codice, con la colonna da ordinare, da che parte (crescente o decrescente) e c'è un'intestazione.
Guarda un video
- Il registratore di macro Excel non fa un buon lavoro con l'ordinamento delle registrazioni.
- A condizione che i dati possano essere selezionati utilizzando Ctrl + * (nota come regione corrente)
- A condizione che tu non stia ordinando per colore o icona o più di tre livelli
- Utilizzare il metodo Range (). CurrentRegion.Sort della vecchia scuola in Excel
Trascrizione del video
Impara Excel da MrExcel Podcast, episodio 2093: Ordinamento con VBA
Ehi, bentornato al netcast, sono Bill Jelen. La domanda di oggi di James a Huntsville. James, ho usato il registratore di macro per registrare l'azione di ordinamento dei dati. Quindi diciamo che James aveva bisogno di ordinare questi dati per settore e clienti. Quindi vieni qui alla scheda Visualizza, Macro, Registra nuova macro, SortMyReports, Tasto di scelta rapida Ctrl - Digiterò Shift + S lì e farò clic su OK. Va bene, quindi da qui facciamo: Dati, Ordina, e vogliamo dire che vogliamo Ordina per settore, quindi Aggiungi un livello e Ordina per cliente e fare clic su OK. In basso nell'angolo inferiore sinistro facciamo clic su Interrompi registrazione. Va bene, quindi eccolo. Sembra aver funzionato, giusto?
Ma ecco il problema: domani avrai più o meno dati o, sai, qualunque cosa. E quella Macro registrata è semplicemente orribile. Diamo un'occhiata, farò Alt + F8 e daremo un'occhiata a SortMyReport, lo modificherò. Va bene, e questo è tutto ciò che hanno registrato quindi SortFields.Clear, e poi hanno impostato un nuovo ordinamento con SortFields.Add e sono hard-coded, e ci sono 568 righe e tutto il resto.
Ora, riordinando nel corso della giornata, è stato davvero molto facile. Bene, e poi in Excel 2007 hanno aggiunto Ordina per icona, Ordina per colore, Ordina per colore carattere, la possibilità di avere 15 ordinamenti di livello e tutto è diventato davvero, davvero folle. Quindi, non uso più la macro registrata. Vado solo alla vecchia scuola.
Ora tornerò a Excel. Ecco le regole per far funzionare questo smistamento della vecchia scuola, va bene. Intestazione sopra ogni colonna: l'intestazione deve essere su una riga, non su due righe. Se hai titoli lassù e va bene avere titoli lassù. Hai bisogno di una riga completamente vuota tra i tuoi titoli e la prima intestazione. Se hai degli appunti sul lato destro: tua moglie chiama con una lista della spesa: “Ehi, tesoro, fermati mentre torni a casa. Prendi latte, uova e vodka. " Ci deve essere una colonna completamente vuota tra i tuoi dati e quello. E se ci sono note standard in fondo, assicurati che ci sia una riga completamente vuota tra l'ultimo bit di dati e quelle note.
Il mio obiettivo è che dovremmo essere in grado di arrivare a qualsiasi cella: la cella in alto a sinistra contiene questi dati e premi Ctrl + * e selezionerà i dati da ordinare. Ora premerò Ctrl +. che ci porta a questo angolo e poi Ctrl +. ci porterà nell'angolo in basso a destra, Ctrl +. ci porta all'angolo in basso a sinistra. Va bene, quindi se Ctrl + * selezionerà correttamente i tuoi dati, allora tutto sarà fantastico. Se metti la tua lista della spesa nella colonna H e vediamo che veniamo qui e Ctrl + *, beh ora, stiamo ordinando la lista della spesa come parte della cosa e la tua lista della spesa verrà incasinata. Oppure annulleremo: se questa riga non è qui, ora facciamo Ctrl + *, vedi siamo- ora saranno all'oscuro perché non hanno più intestazioni, va bene?
Quindi, se intendi utilizzare il mio codice assicurati che tutte queste regole siano vere: nessun ordinamento del mio colore, nessun ordinamento della mia icona, 3 o meno livelli di ordinamento. Annulla, va bene. Quindi ecco quello che sappiamo: sappiamo che ogni giorno i nostri dati inizieranno in A5. Se non sappiamo quante righe o quante, beh anche quante colonne potremmo avere. Non riesco a immaginare una situazione in cui le colonne vengono scambiate, ma sicuramente il numero di righe cambierà. Quindi Alt + F11, inizieremo semplicemente da quella cella nell'angolo in alto a sinistra. Quindi Range, nel mio caso è "A5" .CurrentRegion. La regione attuale è questa fantastica proprietà di edificio che dice che premeremo Ctrl + Maiusc + * e tutto ciò che è incluso lì sarà ciò che verrà ordinato. E lo facciamo. .Ordina, va bene.
Ora, ecco il punto. Se vuoi fare un ordinamento a un livello è facile: Key1: =. : = e diciamo solo che sarà Range - Oh, dimentico di cosa si tratta. Era Sector, dov'è Sector? Il settore è nella colonna C. Quindi nel mio caso C5, Range ("C5") e quindi Order1: = xlAscending. Ho premuto il tasto Freccia giù lì, e poi Tab. Va bene ora, potrei continuare a uscire a destra ma non lo farò. Vado su una nuova riga quindi spazio, trattino basso per passare a una nuova riga, continua questa riga di codice, va bene? E se ho un ordinamento di secondo livello: Key2: = e in questo caso voglio ordinare per cliente che si trova nella colonna D, quindi D5. E poi, Order2: xlAscending. Bellissimo.
Non ho un ordinamento di terzo livello, ma se lo avessi, sarebbe Key3 e poi Order3. E poi il prossimo, quello che devi fare è Header, va bene? Quindi, Header: = xlGuess è lì che stai ricevendo un sacco di problemi. E quindi diremo xlSì, sicuramente come intestazione. Anche ai vecchi tempi, il registratore di macro utilizzava xlGuess. Odio che Excel indovini.
Questo è tutto. Una riga di codice, è tutto ciò che devi fare e funzionerà con più righe, meno righe. È una cosa bellissima, bellissima. Va bene, quindi torneremo qui su Excel. Ctrl + Maiusc + S è ancora la cosa assegnata. Ora- Se sei appena passato a VBA e lo digiti tu stesso, puoi andare su Alt + F8, trovare il nome della tua Macro, fare clic su Opzioni e digitare Ctrl + Maiusc + S lì o possiamo persino assegnarlo a un Tasto di scelta rapida qui sopra sulla barra di accesso rapido. Fare clic con il tasto destro, Personalizza la barra degli strumenti di accesso rapido dove scelgo tra le nostre macro. Ho una macro chiamata SortMyReport, farà clic su Aggiungi - Odio il piccolo diagramma di flusso lì. Lo modificheremo e mi piacerebbe che ci fosse una sorta di situazione dalla A alla Z ma ovviamente non c'è. Forse quella freccia che sa, chissà, sceglie qualsiasi cosa.La magica palla 8, non lo so. Sceglierò questo piccoletto qui, fai clic su OK, fai clic su OK. Va bene, quindi ora i nostri dati sono ordinati per data, scelgo io e non importa cosa scelgo. Torno sempre indietro e lo ordina da A5, faccio clic sul piccoletto ei miei dati sono ora ordinati per settore, all'interno del settore, per cliente. Funziona alla grande, va bene?
Quindi, se sei un fan del registratore di macro, i miei più sentiti auguri a te. Ma il codice del registratore di macro in questi giorni per l'ordinamento in VBA è molto più semplice; per tornare indietro, usa semplicemente essenzialmente questa, una riga di codice.
Bene, di solito questo è il punto in cui cerco di farti comprare questo libro, ma oggi penso che dovresti dare un'occhiata a questo libro: Excel 2016 VBA e macro di Tracy e me stesso. Wow! Controllalo. Non mi rendevo conto che esiste una versione in un'altra lingua. Ti porteremo completamente al livello della curva di apprendimento macro dalla registrazione della tua prima macro al codice che ti serve.
Bene, semplice conclusione per oggi: il registratore di macro di Excel non fa un buon lavoro con la registrazione e l'ordinamento: a condizione che i tuoi dati possano essere selezionati usando Ctrl + * che è noto come la regione corrente, a condizione che tu non stia ordinando come colore o icona o più di tre livelli, usa semplicemente il metodo Range (). CurrentRegion.Sort della vecchia scuola in VBA per ordinare.
Voglio ringraziare James per avermi inviato questa domanda. Voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2093.xlsm
