Excel VBA-makro för att sortera data. Jag gillar inte hur makroinspelaren skapar extra kod för sortering. Sortering i Excel VBA ska vara enkel. En rad kod, med kolumnen att sortera, vilken väg (stigande eller fallande) och finns det en rubrik.
Kolla på video
- Excel-makroinspelaren gör inte ett bra jobb med inspelningssortering.
- Förutsatt att dina data kan väljas med Ctrl + * (känd som den aktuella regionen)
- Förutsatt att du inte sorterar efter färg eller ikon eller mer än tre nivåer
- Använd den gamla skolan Range (). CurrentRegion.Sort-metod i Excel
Videoutskrift
Lär dig Excel från MrExcel Podcast, avsnitt 2093: Sortera med VBA
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga från James i Huntsville. James, jag använde makroinspelaren för att spela in åtgärden med att sortera data. Så låt oss säga att James behövde sortera dessa data efter sektor och kunder. Så du kommer hit till fliken Visa, makron, spela in ny makro, SortMyReports, genväg Ctrl - jag skriver Shift + S där och klickar på OK. Okej, så härifrån gör vi: Data, Sortera, och vi vill säga att vi vill sortera efter sektor och sedan lägga till en nivå och sortera efter kund och klicka på OK. Nere i det nedre vänstra hörnet klickar vi på Stoppa inspelning. Okej, så där är det. Verkar ha fungerat, eller hur?
Men här är problemet: imorgon kommer du att ha mer data eller mindre data eller, du vet, vad som helst. Och den inspelade makroen är bara hemsk. Låt oss ta en titt, jag ska göra Alt + F8 och titta på SortMyReport, kommer att redigera det. Okej, och det här är allt de spelade in så att SortFields.Clear, och sedan skapade de en ny sort med SortFields.Add och de är hårda kodade, och det finns 568 rader och alla grejer.
Nu sorterade jag tillbaka på dagen var jag verkligen, väldigt lätt. Okej, och sedan i Excel 2007 lade de till Sortera efter ikon, Sortera efter färg, Sortera efter teckensnittsfärg, möjligheten att ha 15 nivåssorter och allt blev verkligen riktigt galen. Så jag använder inte den inspelade makroen längre. Jag går bara på skolan.
Nu ska jag byta tillbaka till Excel. Här är reglerna för att denna old-school-sortering ska fungera, okej. Rubrik ovanför varje kolumn: rubriken måste vara på en rad, inte två rader. Om du har titlar där uppe och det är bra att ha titlar där uppe. Du behöver en helt tom rad mellan dina titlar och den första rubriken. Om du har anteckningar på höger sida: din fru ringer med en livsmedelslista, "Hej älskling, sluta på vägen hem. Få mjölk, ägg och vodka. ” Det måste finnas en helt tom kolumn mellan dina data och det. Och om det finns anteckningar på pannan längst ner, se till att det finns en helt tom rad mellan den sista biten av data och dessa anteckningar.
Hela mitt mål är att vi ska kunna komma till en cell: den övre vänstra hörnet cellen dessa data och tryck Ctrl + * och det kommer att välja de data som ska sorteras. Nu ska jag trycka på Ctrl +. som tar oss till det här hörnet och sedan Ctrl +. tar oss till det nedre högra hörnet, Ctrl +. tar oss till det nedre vänstra hörnet. Okej, så om Ctrl + * kommer att välja dina data korrekt är allt bra. Om du lägger din livsmedelslista i kolumn H och vi ser att vi kommer upp hit och Ctrl + *, nu, vi sorterar livsmedelslistan som en del av saken och din livsmedelslista kommer att bli trasig. Eller så kommer vi att ångra: om den här raden inte är här, gör vi nu Ctrl + *, se att vi är - nu kommer de att vara oklara eftersom de inte har några rubriker längre, okej?
Så om du ska använda min kod, se till att alla dessa regler är sanna: ingen sortering av min färg, ingen sortering av min ikon, 3 eller mindre sorteringsnivåer. Ångra, okej. Så här är vad vi vet: vi vet att varje dag kommer våra data att börja i A5. Om vi inte vet hur många rader eller hur många - ja, även hur många kolumner vi kan ha. Jag kan inte föreställa mig en situation där kolumnerna byts ut, men säkert kommer antalet rader att ändras. Så Alt + F11, vi kommer helt enkelt att börja från den högra vänstra hörncellen. Så Range, i mitt fall är "A5" .CurrentRegion. Nuvarande region är denna fantastiska byggnadsfastighet som säger att vi ska trycka på Ctrl + Skift + * och allt som ingår där är vad som kommer att sorteras. Och det gör vi .Sort. .Sort, okej.
Nu är det här. Om du vill göra en nivå på en nivå är det enkelt: Key1: =. : = och vi säger bara att det kommer att bli Range - Åh jag glömmer vad det är. Det var sektor, var är sektor? Sektor finns i kolumn C. Så C5 i mitt fall, Range (“C5”) och sedan, Order1: = xlAscending. Jag tryckte på nedåtpilen där och sedan på Tab. Okej nu, jag kan fortsätta att gå ut till höger men jag ska inte göra det. Jag tänker gå till en ny rad så utrymme, understryk för att gå till en ny rad, fortsätter denna rad kod, okej? Och om jag har en andra nivå sortering: Key2: = och i det här fallet vill jag sortera efter kund som finns i kolumn D, så D5. Och sedan, Order2: xlAscending. Skön.
Jag har ingen tredje nivå sortering men om du gjorde det skulle det vara Key3 och sedan Order3. Och sedan den här nästa, den du måste göra är Header, okej? Så, rubrik: = xlGuess det är där du får en massa problem. Och så kommer vi att säga xlJa där, definitivt som en rubrik. Även i gamla tider skulle Macro Recorder använda xlGuess. Jag hatar för Excel att gissa.
Det är allt. En kodrad, det är allt du behöver göra och det fungerar med fler rader, mindre rader. Det är en vacker, vacker sak. Okej, så vi kommer tillbaka hit till Excel. Ctrl + Skift + S är fortfarande den sak som tilldelats. Det nu - Om du precis bytte till VBA och skriver det själv kan du gå till Alt + F8, hitta namnet på din makro, klicka på Alternativ och skriva Ctrl + Skift + S där inne eller så kan vi till och med tilldela det till en Genväg här uppe i snabbåtkomstverktygsfältet. Högerklicka, Anpassa snabbåtkomstverktygsfältet där jag väljer bland våra makron. Jag har en makro som heter SortMyReport, kommer att klicka på Lägg till - jag hatar det lilla flödesschemat där. Vi kommer att modifiera det och jag skulle gärna vilja att det skulle finnas någon slags A till Z-situation där, men det är naturligtvis inte. Kanske den pilen som vet, vem vet, välj bara vad som helst.Den magiska 8-kulan vet jag inte. Jag ska välja den här lilla killen här, klicka OK, klicka OK. Okej, så nu är våra data sorterade efter datum, jag väljer - och det spelar ingen roll vad jag väljer. Det kommer alltid att gå tillbaka och sortera det från A5, jag klickar på den lilla killen och mina data sorteras nu efter sektor, inom sektor, efter kund. Det fungerar bra, okej?
Så om du är ett fan av Macro Recorder, mina hjärtliga hälsningar till dig. Men Macro Recorder-koden i dessa dagar för sortering i VBA är bara så mycket enklare; för att bara gå tillbaka, använd helt enkelt den här, en kodrad.
Det här är vanligtvis den plats där jag försöker få dig att köpa den här boken, men idag tycker jag att du bör ta en titt på den här boken: Excel 2016 VBA och Macros av Tracy och jag själv. Wow! Kolla in det här. Jag insåg inte att det finns en version på ett annat språk. Vi tar dig helt upp i Macro Learning Curve från att spela in din första makro till den kod du behöver.
Tja, enkel förpackning för idag: Excel Macro Recorder gör inte ett bra jobb med inspelning, sortering: förutsatt att dina data kan väljas med Ctrl + * som kallas den aktuella regionen, förutsatt att du inte sorterar som färg eller ikon eller mer än tre nivåer, använd bara den gamla skolan Range (). CurrentRegion.Sort-metod i VBA för att sortera.
Jag vill tacka James för att han skickade den frågan. Jag vill tacka dig för att du kom förbi. Vi ses nästa gång för en ny netcast från.
Nedladdning fil
Ladda ner exempelfilen här: Podcast2093.xlsm
