
Nästan alla makron som arbetar med en lista behöver förr eller senare veta var datan tar slut. Ska du formatera, kopiera eller fylla på med nya rader måste koden veta hur långt ner den ska gå. Annars får du välja mellan att gissa och att låta makrot tugga sig igenom hela kolumnen, vilket både tar tid och kan ge fel resultat.
I den här artikeln går jag igenom hur du med en enda rad VBA-kod hittar den sista använda raden i en kolumn och hur du får fram den första lediga raden när du vill lägga till data längst ner. Jag tar också upp de fallgropar som är bra att känna till, till exempel tomma kolumner och filtrerade rader, och jämför med några andra metoder.
Varför behöver makrot veta var sista raden är?
Nästan varje makro som bearbetar en lista stöter på samma fråga: hur långt ner går datan? Antalet rader ändras från gång till gång. I dag har exportfilen 214 rader, nästa vecka kanske 3 000. Om koden förutsätter ett fast antal rader blir det fel så fort listan växer eller krymper. Det här är några vanliga situationer där makrot behöver veta var sista raden är:
- Formatering, till exempel valutaformat, kantlinjer eller villkorsstyrd formatering på exakt de rader som innehåller data.
- Kopiering av ett dataområde till ett annat blad eller en annan arbetsbok, utan att få med tusentals tomma rader.
- Formler i grannkolumner, till exempel en beräkning i kolumn D som ska fyllas ner lika långt som datan i kolumn A–C.
- Import av data från andra filer eller system, där du inte vet i förväg hur många rader som kommer in. Ofta ska den nya datan läggas under den gamla.
Varför inte bara ta hela kolumnen?
Det kan verka enklast att låta makrot jobba mot hela kolumnen, alltså Range("A:A"). Men i Excel 2007 och senare har ett kalkylblad 1 048 576 rader, och i äldre versioner (.xls-formatet) 65 536. Det ger flera problem:
- Det går långsamt. En loop som går igenom hela kolumnen behandlar över en miljon celler, även om bara några hundra innehåller något. Att skriva en formel i en hel kolumn ger lika många formler som Excel sedan måste räkna om.
- Filen kan svälla. Formatering och formler som läggs ända ner till sista raden gör att Excel betraktar ett mycket större område som använt. Filen blir större och långsammare att öppna och spara.
- Kopieringen kan misslyckas. Om du kopierar en hel kolumn och klistrar in den någonstans utanför rad 1 får inte alla rader plats, och Excel avbryter med ett felmeddelande.
- Risk att skriva över data. Om makrot fyller i eller rensar celler i ett för stort område kan det ändra saker som ligger längre ner på bladet, till exempel anteckningar eller summeringar som någon har lagt under listan.
Det säkraste är alltså att låta koden ta reda på var datan slutar och sedan bara arbeta med just det området. Hur du gör det visar nästa avsnitt.
Hitta sista använda raden med End(xlUp)
Den klassiska lösningen ser ut så här. Det är samma rad som jag själv har använt i många år:
intSistarad = Columns("A:A").Range("A65536").End(xlUp).Row
Koden gör samma sak som när du själv letar upp slutet på en lista med tangentbordet:
- Range(”A65536”) pekar ut den allra sista cellen i kolumn A, så som det såg ut i Excel 2003 och tidigare.
- .End(xlUp) motsvarar att trycka Ctrl+Pil upp när den cellen är markerad. Excel hoppar då uppåt och stannar på den första cellen som innehåller något, alltså den sista ifyllda cellen i kolumnen.
- .Row returnerar radnumret för den cellen. Det är det talet som sparas i variabeln.
Att börja nerifrån och gå uppåt har en fördel. Tomma celler mitt i listan stoppar inte sökningen, vilket de hade gjort om du gått nedåt från rad 1 med Ctrl+Pil ned.
Problemet med A65536
Radnumret 65536 är hårdkodat. Sedan Excel 2007 har ett kalkylblad 1 048 576 rader. Om din data fortsätter förbi rad 65 536 börjar sökningen mitt i datan, och då får du fel svar. Prefixet int i variabelnamnet antyder dessutom att variabeln har deklarerats som Integer. En sådan variabel klarar bara tal upp till 32 767, så makrot kraschar med ett spillfel när listan blir längre än så.
En modern och säkrare variant
Låt i stället Excel tala om hur många rader bladet har med Rows.Count. Spara resultatet i en Long, och ange vilket kalkylblad som avses, så att koden inte råkar läsa det blad som råkar vara aktivt:
Dim ws As Worksheet
Dim lngSistaRad As Long
Set ws = ThisWorkbook.Worksheets("Data")
lngSistaRad = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Cells(ws.Rows.Count, "A") är den sista cellen i kolumn A, oavsett Excelversion. Resten fungerar precis som i originalkoden. Lägg märke till att även Rows.Count föregås av ws., så att radantalet hämtas från samma blad.
Exempel
I exemplet nedan finns en orderlista på bladet Data. Rubriken står på rad 1 och den sista ordern på rad 8.

När koden körs startar den i A1048576, hoppar uppåt och landar i A8. Variabeln lngSistaRad får därmed värdet 8. Det talet kan du sedan använda i ett område som ws.Range("A2:C" & lngSistaRad), som här blir A2:C8. Då arbetar makrot med exakt de rader som innehåller data.
Få fram första lediga raden
När du vet vilken rad som är den sista använda är det bara ett steg kvar till den första lediga raden. Den ligger direkt under, så du lägger till 1 till variabeln:
intSistarad = intSistarad + 1
Om datan i kolumn A slutar på rad 8 innehåller variabeln nu 9, alltså den första tomma cellen under listan. Där kan makrot skriva nya värden utan att något befintligt skrivs över.
Med den moderna varianten från föregående avsnitt kan du lika gärna göra additionen direkt i samma rad:
lngForstaLediga = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
Här är ett kort exempel som lägger till en ny post längst ner i en lista med namn i kolumn A och belopp i kolumn B:
Sub LaggTillPost()
Dim ws As Worksheet
Dim lngRad As Long
Set ws = ThisWorkbook.Worksheets("Data")
lngRad = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
ws.Cells(lngRad, "A").Value = "Anna Berg"
ws.Cells(lngRad, "B").Value = 1250
End Sub
Varje gång makrot körs hamnar den nya posten på raden under den senaste, så listan växer nedåt utan att du behöver hålla reda på radnumret själv. Det är samma mönster du använder när du importerar data från en annan fil eller samlar inmatningar från ett formulär. Positionen räknas fram på nytt vid varje körning, så det spelar ingen roll om listan har 10 eller 10 000 rader.
Fallgropar att känna till
Metoden med End(xlUp) fungerar i de allra flesta fall, men den har några kända svagheter. Det är bra att känna till dem innan makrot körs på riktig data.
Tom kolumn ger rad 1
Om kolumnen är helt tom går End(xlUp) hela vägen upp till rad 1 och stannar där. Resultatet blir alltså 1, trots att A1 också är tom. Om koden sedan adderar 1 för att hitta första lediga raden hamnar det nya värdet på rad 2 i stället för rad 1. Kan kolumnen vara tom, till exempel i ett nytt importblad, bör du kontrollera det först. Ett enkelt sätt är If WorksheetFunction.CountA(ws.Columns("A")) = 0 Then. Ett annat är att testa om den hittade cellen är tom med IsEmpty.
Filtrerade och dolda rader
End(xlUp) beter sig som Ctrl+↑. När ett autofilter är aktivt kan den därför stanna på sista synliga raden i stället för den verkliga sista raden. Rader som döljs av filtret längst ner i listan räknas då inte med. Manuellt dolda rader brukar inte ställa till det på samma sätt, men det är klokt att testa på din egen fil. Om makrot kan köras på ett filtrerat blad kan du ta bort filtret först med If ws.FilterMode Then ws.ShowAllData. Ett alternativ är att använda en annan metod, se nästa avsnitt.
Formler som returnerar tom sträng
En cell som innehåller en formel som =OM(B2="";"";B2*2) ser tom ut när formeln returnerar "". För Excel är den ändå inte tom, eftersom den innehåller en formel. End(xlUp) räknar därför med sådana celler. Har du dragit ut formler till rad 500 men bara har data till rad 120 får du 500 som sista rad. Välj i så fall en kolumn med inskrivna värden i stället för formler. Du kan också stega uppåt från den hittade raden så länge cellens värde är "".
Välj en kolumn som alltid är ifylld
Resultatet blir aldrig bättre än kolumnen du mäter i. Om kolumn A ibland saknar värde på sista raden missar du den raden. Välj därför en kolumn som alltid fylls i, till exempel ett id, ett ordernummer eller ett datum. Luckor mitt i kolumnen spelar däremot ingen roll, eftersom sökningen börjar längst ner och går uppåt. Det är en viktig skillnad mot End(xlDown), som stannar vid första tomma cellen.
Varför metoden ändå är förstahandsvalet
Trots fallgroparna använder jag och många andra VBA-programmerare den här raden i nästan varje makro, av två skäl:
- Snabbhet: Koden gör ett enda anrop och Excel hoppar direkt till rätt cell. Ingen loop går igenom raderna en och en, så tiden är i stort sett densamma oavsett om listan har 10 eller 100 000 rader.
- Enkelhet: Allt ryms på en rad som är lätt att läsa och känna igen. Du byter bara kolumnbokstaven för att mäta i en annan kolumn, och logiken går att kontrollera för hand med Ctrl+↑.
Andra metoder: Find, UsedRange och loopar
End(xlUp) är inte det enda sättet att hitta var datan slutar. Här är tre alternativ som du stöter på i andras kod, och när de passar.
Find: sista raden i hela bladet
End(xlUp) tittar bara på en kolumn. Om du vill veta vilken rad som är den sista med innehåll i någon kolumn kan du använda Find och söka baklänges efter vad som helst:
Set rng = ws.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Sökningen börjar i praktiken längst ner och går uppåt rad för rad. Den första cellen som innehåller något är alltså den på den sista använda raden. Två saker är bra att känna till:
- Om bladet är helt tomt hittar
Findingenting och returnerarNothing. Kontrollera därför medIf Not rng Is Nothing Then lastRow = rng.Rowinnan du läser av radnumret, annars får du ett körfel. Findkommer ihåg inställningar från den senaste sökningen, även sådana som gjorts i dialogrutan Sök i Excel. Ange därför argumenten somLookInuttryckligen i stället för att lita på standardvärdena.
Metoden är lika snabb som End(xlUp) och är det säkraste valet när du inte vet vilken kolumn som alltid är ifylld.
UsedRange: snabbt men opålitligt
Varje kalkylblad har egenskapen UsedRange, det område som Excel anser vara använt. Sista raden får du med:
lastRow = ws.UsedRange.Row + ws.UsedRange.Rows.Count - 1
Lägg märke till att det inte räcker med UsedRange.Rows.Count. Om området börjar på rad 3 får du antalet rader och inte radnumret för den sista raden.
Det större problemet är att Excel inte alltid krymper UsedRange när du raderar data. Har du tömt de sista 500 raderna kan de fortfarande räknas som använda tills arbetsboken sparats. Celler som bara har formatering, till exempel en kantlinje eller fyllnadsfärg, räknas också. Resultatet blir att UsedRange ofta pekar för långt ner. Metoden passar bäst när du vill ha en grov uppfattning om bladets storlek, inte när radnumret ska användas för att skriva data.
Loopa cell för cell
Det går också att gå nedåt i kolumnen tills du hittar en tom cell, till exempel med Do While ws.Cells(r, "A").Value <> "": r = r + 1: Loop. Koden är lätt att följa, men den har två nackdelar:
- Den är långsam. Varje varv läser en cell från kalkylbladet, och med tiotusentals rader märks det tydligt.
- Den stannar vid den första tomma cellen. Har listan en tom rad mitt i får du fel svar, vilket End(xlUp) undviker genom att gå nedifrån.
En loop behövs egentligen bara när villkoret är mer komplicerat än ”cellen är inte tom”, till exempel när du vill hitta sista raden där en viss kolumn har ett bestämt värde.
Vilken metod ska du välja?
- End(xlUp): förstahandsvalet när du har en kolumn som alltid är ifylld, till exempel ett id-nummer eller ett datum.
- Find: när du behöver sista raden i hela bladet eller inte vet vilken kolumn som är komplett.
- UsedRange: bara för en ungefärlig överblick. Lita inte på den efter att data har raderats.
- Loop: när du letar efter något annat än sista ifyllda cellen och de andra metoderna inte räcker.
Sammanfattning
För de flesta makron räcker en enda rad för att hitta var datan slutar. Välj en kolumn som alltid är ifylld, ange vilket kalkylblad du menar och spara resultatet i en variabel av typen Long:
sistaRad = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Behöver du första lediga raden lägger du till 1. Ska du i stället ha den sista raden i hela bladet, oavsett kolumn, är Find ett bättre val.
Skriv alltid Rows.Count i stället för ett hårdkodat radnummer som 65536 eller 1048576. Då anpassar sig koden till den Excelversion och det filformat den körs i. Ett fast radnummer kan missa data längre ner i bladet eller ge fel när makrot körs i en annan version.