Excel VBA-makro for å sortere data. Jeg liker ikke hvordan makroopptakeren lager ekstra kode for sortering. Sortering i Excel VBA skal være enkel. Én kodelinje, med kolonnen som skal sorteres, hvilken vei (stigende eller synkende) og er det en overskrift.
Se på video
- Excel-makroopptakeren gjør ikke en god jobb med opptakssortering.
- Forutsatt at dataene dine kan velges ved hjelp av Ctrl + * (kjent som gjeldende region)
- Forutsatt at du ikke sorterer etter farge eller ikon eller mer enn tre nivåer
- Bruk den gamle skolen Range (). CurrentRegion.Sort-metode i Excel
Videoutskrift
Lær Excel fra MrExcel Podcast, episode 2093: Sortering med VBA
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål fra James i Huntsville. James, jeg brukte makroopptakeren til å registrere handlingen med å sortere data. Så la oss si at James trengte å sortere disse dataene etter sektor og kunder. Så du kommer hit til Vis-fanen, Makroer, Ta opp ny makro, SortMyReports, hurtigtast Ctrl - Jeg skriver Shift + S der og klikker OK. OK, så herfra gjør vi: Data, Sorter, og vi vil si at vi vil sortere etter sektor og deretter legge til et nivå og sortere etter kunde, og klikke OK. Nede i nederste venstre hjørne klikker vi på Stopp opptak. OK, så det er det. Ser ut til å ha fungert, ikke sant?
Men her er problemet: i morgen kommer du til å ha mer data eller mindre data, eller vet du hva som helst. Og den innspilte makroen er bare fryktelig. La oss ta en titt, jeg skal gjøre Alt + F8 og se på SortMyReport, vil redigere det. Greit, og dette er alt de registrerte så SortFields.Clear, og så satte de opp en ny sort med SortFields.Add, og de er hardkodede, og det er 568 rader, og alt av ting.
Nå som jeg sorterte tilbake på dagen, var jeg veldig, veldig lett. Greit, og i Excel 2007 la de til Sorter etter ikon, Sorter etter farge, Sorter etter skriftfarge, muligheten til å ha 15 nivåer og alt ble virkelig vanvittig. Så jeg bruker ikke den innspilte makroen lenger. Jeg går på old school.
Nå skal jeg bytte tilbake til Excel. Her er reglene for at denne old-school-sorteringen skal fungere, ok. Overskrift over hver kolonne: overskriften må være på en rad, ikke to rader. Hvis du har titler der oppe, og det er greit å ha titler der oppe. Du trenger en helt tom rad mellom titlene og den første overskriften. Hvis du har notater på høyre side: din kone ringer med en dagligvareliste, “Hei, skat, stopp på vei hjem. Få melk, egg og vodka. ” Det må være en helt tom kolonne mellom dataene dine og det. Og hvis det er noter på kjelene nederst, må du sørge for at det er en helt tom rad mellom den siste biten av data og disse notatene.
Hele målet mitt er at vi skal kunne komme til en hvilken som helst celle: øverste venstre hjørne celle disse dataene og trykk Ctrl + * og den vil velge dataene som skal sorteres. Nå skal jeg trykke Ctrl +. som tar oss til dette hjørnet og deretter Ctrl +. tar oss til nederst til høyre, Ctrl +. tar oss til nederste venstre hjørne. OK, så hvis Ctrl + * vil velge dataene dine riktig, er alt bra. Hvis du legger dagligvarelisten din i kolonne H, og vi ser at vi kommer opp hit og Ctrl + *, nå, vi sorterer dagligvarelisten som en del av saken, og dagligvarelisten din blir skrudd opp. Eller vi vil angre: Hvis denne raden ikke er her, gjør vi nå Ctrl + *, se vi er - nå vil de være uklare fordi de ikke har noen overskrifter lenger, ok?
Så hvis du skal bruke koden min, må du sørge for at alle disse reglene stemmer: ingen sortering av fargen min, ingen sortering av ikonet mitt, 3 eller mindre sorteringsnivåer. Angre, ok. Så her er hva vi vet: vi vet at dataene våre hver dag kommer til å starte i A5. Hvis vi ikke vet hvor mange rader eller hvor mange - til og med hvor mange kolonner vi kan ha. Jeg kan ikke forestille meg en situasjon der kolonnene byttes ut, men absolutt antall rader kommer til å endres. Så Alt + F11, vi kommer rett og slett til å starte fra den øverste venstre hjørne cellen. Så Range, i mitt tilfelle er "A5" .CurrentRegion. Nåværende region er denne fantastiske bygningseiendommen som sier at vi skal trykke Ctrl + Shift + *, og alt som er inkludert der er det som skal sorteres. Og det gjør vi .Sort. .Sortert, ok.
Nå, her er tingen. Hvis du ønsker å gjøre en sortering på ett nivå, er det enkelt: Nøkkel1: =. : = og vi sier bare at det kommer til å bli Range - Åh jeg glemmer hva det er. Det var sektor, hvor er sektor? Sektor er i kolonne C. Så C5 i mitt tilfelle, Range (“C5”) og deretter, Order1: = xlAscending. Jeg trykket på pil ned der og deretter Tab. Greit nå, jeg kunne fortsette å gå ut til høyre, men jeg skal ikke gjøre det. Jeg kommer til å gå til en ny linje, så plass, understrek for å gå til en ny linje, fortsetter denne linjen med kode, ok? Og hvis jeg har en annen nivå sortering: Key2: = og i dette tilfellet vil jeg sortere etter kunde som er i kolonne D, så D5. Og så, Order2: xlAscending. Vakker.
Jeg har ikke et tredje nivå sortering, men hvis du gjorde det, ville det være Key3 og deretter Order3. Og så er denne neste, den du må gjøre, Header, ok? Så, overskrift: = xlGuess det er der du får mye trøbbel. Og så skal vi si xlJa der, definitivt som en overskrift. Selv i gamle dager ville Macro Recorder bruke xlGuess. Jeg hater for Excel å gjette.
Det er det. Én linje med kode, det er alt du trenger å gjøre, og den vil fungere med flere rader, færre rader. Det er en vakker, vakker ting. Greit, så vi kommer tilbake hit til Excel. Ctrl + Shift + S er fortsatt det som er tildelt. Det nå - Hvis du nettopp byttet til VBA og skriver det selv, kan du gå til Alt + F8, finne navnet på makroen din, klikke Alternativer og skrive Ctrl + Shift + S der inne, eller vi kan til og med tildele det til en Hurtigtast her oppe på verktøylinjen for hurtig tilgang. Høyreklikk, Tilpass hurtig tilgangsverktøylinjen der jeg velger fra våre makroer. Jeg har en makro som heter SortMyReport, vil klikke på Legg til - jeg hater det lille flytskjemaet der. Vi vil endre det, og jeg vil gjerne at det skal være en slags A til Å-situasjon der, men selvfølgelig er det ikke. Kanskje den pilen som vet, hvem vet, bare velg hva som helst.Den magiske 8-ballen vet jeg ikke. Jeg skal velge denne lille fyren her, klikk OK, klikk OK. OK, så nå er dataene våre sortert etter dato, jeg velger - og det spiller ingen rolle hva jeg velger. Det kommer alltid til å gå tilbake og sortere det fra A5, jeg klikker på den lille fyren og dataene mine er nå sortert etter sektor, innen sektor, etter kunde. Det fungerer bra, ok?
Så hvis du er en fan av Macro Recorder, så ønsker jeg deg hjertelig lykke til. Men Macro Recorder-koden i disse dager for sortering i VBA er bare så mye enklere; for å bare gå tilbake, bruk ganske enkelt denne, en linje kode.
Vel, dette er vanligvis stedet hvor jeg prøver å få deg til å kjøpe denne boken, men i dag synes jeg du bør ta en titt på denne boka: Excel 2016 VBA og Macros av Tracy og meg selv. Wow! Sjekk ut dette. Jeg visste ikke at det er en versjon på et annet språk. Vi får deg helt opp i Macro Learning Curve fra å registrere din første makro til koden du trenger.
Vel, enkel innpakning for i dag: Excel Macro Recorder gjør ikke en god jobb med opptak, sortering: forutsatt at dataene dine kan velges ved hjelp av Ctrl + *, kjent som den nåværende regionen, forutsatt at du ikke sorterer som farge eller ikon eller mer enn tre nivåer, bruk bare den gamle skolen Range (). CurrentRegion.Sort-metode i VBA for å sortere.
Jeg vil takke James for at han sendte spørsmålet inn. Jeg vil takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2093.xlsm
