Arbeta med celler och intervall i Excel VBA (välj, Kopiera, Flytta, redigera)

januari 2, 2021 admin 0 Comments

När du arbetar med Excel spenderas större delen av din tid i kalkylbladsområdet – hantera celler och intervall.

och om du vill automatisera ditt arbete i Excel med VBA, behöver du veta hur man arbetar med celler och intervall med VBA.

det finns många olika saker du kan göra med intervall i VBA (till exempel välj, Kopiera, flytta, redigera etc.).

så för att täcka det här ämnet kommer jag att bryta den här handledningen i sektioner och visa dig hur du arbetar med celler och intervall i Excel VBA med hjälp av exempel.,

låt oss komma igång.

alla koder jag nämner i den här handledningen måste placeras i Vb-redigeraren. Gå till avsnittet ”Var att sätta VBA-koden” för att veta hur det fungerar.

om du är intresserad av att lära sig VBA det enkla sättet, kolla in min Online Excel VBA-träning.

denna handledning omfattar:

välja en Cell/intervall i Excel med VBA

för att arbeta med celler och intervall i Excel med VBA behöver du inte välja den.

i de flesta fall är det bättre att du inte väljer celler eller intervall (som vi kommer att se).,

trots det är det viktigt att du går igenom det här avsnittet och förstår hur det fungerar. Detta kommer att vara avgörande i din VBA lärande och en hel del begrepp som omfattas här kommer att användas i hela denna handledning.

så låt oss börja med ett mycket enkelt exempel.

välja en enda Cell med VBA

om du vill välja en enda cell i det aktiva arket (säg A1) kan du använda följande kod:

Sub SelectCell()Range("A1").SelectEnd Sub

ovanstående kod har den obligatoriska ” Sub ” och ”End Sub” – delen, och en kodlinje som väljer cell A1.,

Range(”A1”) berättar för VBA adressen till cellen som vi vill referera till.

Select är en metod för Områdesobjektet och väljer de celler / intervall som anges i Områdesobjektet. Cellreferenserna måste bifogas i dubbla citat.

den här koden skulle visa ett fel om ett diagramblad är ett aktivt ark. Ett diagramblad innehåller diagram och används inte i stor utsträckning. Eftersom det inte har celler/intervall i det, kan ovanstående kod inte välja det och skulle sluta visa ett fel.

Observera att eftersom du vill välja cellen i det aktiva arket behöver du bara ange celladressen.,

men om du vill välja cellen i ett annat ark (låt oss säga Sheet2) måste du först aktivera Sheet2 och sedan välja cellen i den.

Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

På samma sätt kan du också aktivera en arbetsbok, aktivera ett specifikt kalkylblad i det och välj sedan en cell.

Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub

Observera att när du hänvisar till arbetsböcker måste du använda det fullständiga namnet tillsammans med filtillägget (.XLSX i ovanstående kod). Om arbetsboken aldrig har sparats behöver du inte använda filtillägget.,

nu är dessa exempel inte särskilt användbara, men du kommer att se senare i den här handledningen hur vi kan använda samma begrepp för att kopiera och klistra in celler i Excel (med VBA).

precis som vi väljer en cell kan vi också välja ett intervall.

i händelse av ett intervall kan det vara ett fast storleksområde eller ett variabelt storleksområde.

i ett fast storleksintervall skulle du veta hur stort intervallet är och du kan använda den exakta storleken i din VBA-kod. Men med ett variabla storlekar har du ingen aning om hur stort intervallet är och du behöver använda lite VBA-Magi.

Låt oss se hur man gör det här.,

välja en Fix storlek intervall

här är koden som kommer att välja intervallet A1:d20.

Sub SelectRange()Range("A1:D20").SelectEnd Sub

ett annat sätt att göra detta är att använda nedanstående kod:

Sub SelectRange()Range("A1", "D20").SelectEnd Sub

ovanstående kod tar den övre vänstra celladressen (A1) och den nedre högra celladressen (D20) och väljer hela intervallet. Denna teknik blir användbar när du arbetar med varierande storlek intervall (som vi kommer att se när slutegenskapen omfattas senare i den här handledningen).,

om du vill att valet ska ske i en annan arbetsbok eller ett annat arbetsblad måste du berätta för VBA de exakta namnen på dessa objekt.

till exempel skulle nedanstående kod välja intervallet A1:D20 i Sheet2-kalkylblad i Book2-arbetsboken.

Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub

nu, vad händer om du inte vet hur många rader som finns där. Vad händer om du vill välja alla celler som har ett värde i det.

i dessa fall måste du använda de metoder som visas i nästa avsnitt (om att välja variably sized range).,

välja ett variably Sized Range

det finns olika sätt att välja en rad celler. Metoden du väljer beror på hur data är strukturerad.

i det här avsnittet kommer jag att täcka några användbara tekniker som är väldigt användbara när du arbetar med intervall i VBA.

Välj med CurrentRange Property

i de fall där du inte vet hur många rader/kolumner som har data kan du använda egenskapen CurrentRange för Områdesobjektet.

egenskapen CurrentRange täcker alla sammanhängande fyllda celler i ett dataområde.,

nedan är koden som väljer den aktuella regionen som har cell A1.

Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub

ovanstående metod är bra när du har alla data som en tabell utan några tomma rader / kolumner i den.

men om du har tomma rader/kolumner i dina data kommer det inte att välja dem efter de tomma raderna / kolumnerna. I bilden nedan väljer CurrentRegion-koden data till rad 10 eftersom rad 11 är tom.

i sådana fall kanske du vill använda egenskapen Usedrange för Kalkylbladobjektet.,

Välj använda Usedrange Property

UsedRange låter dig referera till alla celler som har ändrats.

så nedanstående kod skulle välja alla använda celler i det aktiva arket.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Observera att om du har en avlägsen cell som har använts, skulle den övervägas av ovanstående kod och alla celler tills den använda cellen skulle väljas.

välj att använda Slutegenskapen

nu är den här delen väldigt användbar.

med egenskapen End kan du välja den senast fyllda cellen., Detta gör att du kan efterlikna effekten av kontroll ned/Upp pil eller kontroll höger/vänster tangenter.

låt oss försöka förstå detta med ett exempel.

anta att du har en datauppsättning som visas nedan och du vill snabbt välja de senast fyllda cellerna i kolumn A.

problemet här är att data kan ändras och du vet inte hur många celler som fylls. Om du måste göra det med tangentbordet kan du välja cell A1 och sedan använda Control + Down arrow key, och det kommer att välja den senast fyllda cellen i kolumnen.

låt oss nu se hur man gör det med VBA., Denna teknik är praktisk när du snabbt vill hoppa till den sista fyllda cellen i en kolumn med varierande storlek

Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub

ovanstående kod skulle hoppa till den sista fyllda cellen i kolumn A.

på samma sätt kan du använda End(xlToRight) för att hoppa till den sista fyllda cellen i rad.

Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub

nu, vad händer om du vill välja hela kolumnen istället för att hoppa till den sista fyllda cellen.,

Du kan göra det med koden nedan:

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub

i ovanstående kod har vi använt den första och sista referensen för cellen som vi behöver välja. Oavsett hur många fyllda celler som finns där, kommer ovanstående kod att välja alla.

Kom ihåg exemplet ovan där vi valde intervallet A1: D20 genom att använda följande kodlinje:

Range(”A1″,”D20”)

Här A1 var den övre vänstra cellen och D20 var den nedre högra cellen i intervallet. Vi kan använda samma logik vid val av variably storlek intervall., Men eftersom vi inte vet den exakta adressen till den nedre högra cellen, använde vi Slutegenskapen för att få den.

i intervall(”A1”, intervall(”A1”).End (xlDown)), ”A1” avser den första cellen och intervallet (”A1”).End (xlDown) avser den sista cellen. Eftersom vi har angett båda referenserna, väljer Välj metod alla celler mellan dessa två referenser.

På samma sätt kan du också välja en hel datauppsättning som har flera rader och kolumner.

nedanstående kod skulle välja alla fyllda rader / kolumner från cell A1.,

Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub

i ovanstående kod har vi använt Range(”A1”).Slut (xlDown).End (xlToRight) för att få referensen för den nedre högra fyllda cellen i datauppsättningen.

skillnad mellan att använda CurrentRegion och End

om du undrar varför du använder egenskapen End för att välja det fyllda området när vi har egenskapen CurrentRegion, låt mig berätta skillnaden.

med egenskapen End kan du ange startcellen., Om du till exempel har dina data i A1:D20, men den första raden är rubriker, kan du använda slutegenskapen för att välja data utan rubriker (med koden nedan).

Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub

men Currentregionen skulle automatiskt välja hela datauppsättningen, inklusive rubrikerna.

hittills i denna handledning har vi sett hur man hänvisar till en rad celler med olika sätt.

låt oss nu se några sätt där vi faktiskt kan använda dessa tekniker för att få lite arbete gjort.,

kopiera celler/intervall med VBA

som jag nämnde i början av denna handledning är det inte nödvändigt att välja en cell för att utföra åtgärder på den. Du kommer att se i det här avsnittet hur du kopierar celler och intervall utan att ens välja dessa.

låt oss börja med ett enkelt exempel.

kopiera encell

om du vill kopiera cell A1 och klistra in den i cell D1, skulle nedanstående kod göra det.

Sub CopyCell()Range("A1").Copy Range("D1")End Sub

Observera att kopieringsmetoden för områdesobjektet kopierar cellen (precis som Control +C) och klistrar in den i den angivna destinationen.,

i ovanstående exempelkod anges destinationen i samma rad där du använder Kopieringsmetoden. Om du vill göra din kod ännu mer läsbar kan du använda följande kod:

Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub

ovanstående koder kopierar och klistrar in värdet samt formatering / formler i det.

som du kanske redan har märkt kopierar ovanstående kod cellen utan att välja den. Oavsett var du befinner dig i kalkylbladet kopierar koden cell A1 och klistrar in den på D1.

Observera också att ovanstående kod skulle skriva över någon befintlig kod i cell D2., Om du vill att Excel ska låta dig veta om det redan finns något i cell D1 utan att skriva över det, kan du använda koden nedan.

kopiera ett Fixformat område

Om du vill kopiera A1:D20 i J1:M20 kan du använda följande kod:

Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub

i målcellen behöver du bara ange adressen till den övre vänstra cellen. Koden kopierar automatiskt det exakta kopierade området till destinationen.

Du kan använda samma konstruktion för att kopiera data från ett ark till det andra.

nedanstående kod skulle kopiera A1:D20 från det aktiva arket till Arket2.,

Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

ovanstående kopierar data från det aktiva arket. Så se till att arket som har data är det aktiva arket innan du kör koden. För att vara säker kan du också ange kalkylbladets namn när du kopierar data.

Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub

det är bra med ovanstående kod är att oavsett vilket ark som är aktivt, kommer det alltid att kopiera data från Arket1 och klistra in det i Arket2.

Du kan också kopiera ett namngivet intervall genom att använda dess namn istället för referensen.,

om du till exempel har ett namngivet intervall som heter ”SalesData” kan du använda nedanstående kod för att kopiera dessa data till Ark2.

Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub

om omfattningen av det angivna intervallet är hela arbetsboken behöver du inte vara på arket som har det angivna intervallet för att köra den här koden. Eftersom det angivna intervallet är scoped för arbetsboken kan du komma åt det från vilket ark som helst med den här koden.

om du har en tabell med namnet Tabell1 kan du använda nedanstående kod för att kopiera den till Ark2.

Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub

Du kan också kopiera ett intervall till en annan arbetsbok.,

i följande exempel kopierar jag Excel-tabellen (Tabell1) i Book2-arbetsboken.

Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub

den här koden fungerar bara om arbetsboken redan är öppen.

kopiera en variabel storlek intervall

ett sätt att kopiera variabla storlek intervall är att konvertera dessa till namngivna intervall eller Excel-tabell och använda koderna som visas i föregående avsnitt.

men om du inte kan göra det kan du använda den aktuella regionen eller slutegenskapen för områdesobjektet.

nedanstående kod skulle kopiera den aktuella regionen i det aktiva arket och klistra in det i Arket2.,

Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub

om du vill kopiera den första kolumnen i din datamängd till den sista fyllda cellen och klistra in den i Arket2, kan du använda nedanstående kod:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub

Om du vill kopiera raderna samt kolumnerna kan du använda nedanstående kod:

Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub

Observera att alla rader som du vill kopiera är dessa koder väljer inte cellerna medan de körs. I allmänhet hittar du bara en handfull fall där du faktiskt behöver välja en cell / intervall innan du arbetar med det.,

tilldela intervall till Objektvariabler

hittills har vi använt cellernas fullständiga adress (till exempel arbetsböcker(”Book2.xlsx”).Kalkylblad (”Sheet1”).Intervall (’A1’)).

för att göra din kod mer hanterbar kan du tilldela dessa intervall till objektvariabler och sedan använda dessa variabler.

i nedanstående kod har jag till exempel tilldelat käll-och målintervallet till objektvariabler och sedan använt dessa variabler för att kopiera data från ett intervall till ett annat.

vi börjar med att förklara variablerna som Intervallobjekt., Sedan tilldelar vi intervallet till dessa variabler med hjälp av Set-satsen. När intervallet har tilldelats variabeln kan du helt enkelt använda variabeln.

ange Data i nästa tomma Cell (med inmatningsruta)

Du kan använda inmatningsrutorna för att tillåta användaren att ange data.

anta till exempel att du har datauppsättningen nedan och du vill ange försäljningsposten, du kan använda inmatningsrutan i VBA. Med hjälp av en kod kan vi se till att den fyller data i nästa tomma rad.,

ovanstående kod använder VBA-inmatningsrutan för att få ingångarna från användaren och går sedan in i ingångarna i de angivna cellerna.

Observera att vi inte använde exakta cellreferenser. I stället har vi använt egenskapen End and Offset för att hitta den sista tomma cellen och fylla data i den.

den här koden är långt ifrån användbar. Om du till exempel anger en textsträng när inmatningsrutan frågar efter kvantitet eller mängd märker du att Excel tillåter det. Du kan använda ett If-tillstånd för att kontrollera om värdet är numeriskt eller inte och sedan tillåta det i enlighet därmed.,

Looping genom celler / intervall

hittills har vi sett hur man väljer, kopierar och anger data i celler och intervall.

i det här avsnittet kommer vi att se hur man slingrar genom en uppsättning celler/rader/kolumner i ett intervall. Detta kan vara användbart när du vill analysera varje cell och utföra vissa åtgärder baserat på det.

om du till exempel vill markera var tredje rad i urvalet måste du slinga igenom och kontrollera radnumret., På samma sätt, om du vill markera alla negativa celler genom att ändra teckensnittsfärgen till röd, måste du bläddra igenom och analysera varje cells värde.

här är koden som kommer att slingra genom raderna i de valda cellerna och markera alternativa rader.

Sub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.RowsIf Myrow.Row Mod 2 = 0 ThenMyrow.Interior.Color = vbCyanEnd IfNext MyrowEnd Sub

ovanstående kod använder MOD-funktionen för att kontrollera radnumret i valet. Om radnumret är jämnt blir det markerat i cyan-färg.

här är ett annat exempel där koden går igenom varje cell och belyser de celler som har ett negativt värde i det.,

Sub HighlightAlternateRows()Dim Myrange As RangeDim Mycell As RangeSet Myrange = SelectionFor Each Mycell In MyrangeIf Mycell < 0 ThenMycell.Interior.Color = vbRedEnd IfNext MycellEnd Sub

Observera att du kan göra samma sak med villkorlig formatering (vilket är dynamiskt och ett bättre sätt att göra detta). Detta exempel är bara för att visa dig hur looping fungerar med celler och intervall i VBA.

Var ska du lägga VBA-koden

undrar var VBA-koden går i din Excel-arbetsbok?

Excel har en VBA-backend som heter VBA-redigeraren. Du måste kopiera och klistra in koden i VB Editor modul kodfönstret.

här är stegen för att göra detta:

gå till fliken Utvecklare.,
Klicka på Visual Basic-alternativet. Detta öppnar VB-redigeraren i backend.
högerklicka på ett objekt för arbetsboken där du vill infoga koden i projekt Explorer-rutan i Vb-redigeraren. Om du inte ser Projektutforskaren går du till fliken Visa och klickar på Project Explorer.
gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
kopiera och klistra in koden i modulfönstret.,

Du kanske också gillar följande Excel Tutorials:

arbeta med kalkylblad med VBA.
arbeta med arbetsböcker med VBA.
skapa användardefinierade funktioner i Excel.
för nästa slinga i Excel VBA – en nybörjarguide med exempel.
hur du använder Excel VBA Instr funktion (med praktiska exempel).
Excel VBA Msgbox.
så här spelar du in ett makro i Excel.
hur man kör ett makro i Excel.
så här skapar du ett tillägg i Excel.,
Excel Personal Macro Workbook/Save& använd makron i alla arbetsböcker.
Excel VBA händelser – en enkel (och komplett) Guide.
Excel VBA felhantering.
så här sorterar du Data i Excel med VBA (en steg-för-steg-Guide).
24 Användbara Excel makro exempel för VBA nybörjare (klar att använda).