Arbeta med celler och intervall i Excel VBA (Välj, Kopiera, Flytta, Redigera)

När du arbetar med Excel spenderas det mesta av din tid i kalkylbladsområdet - hantering av celler och intervall.

Och om du vill automatisera ditt arbete i Excel med VBA måste du veta hur du arbetar med celler och intervall med VBA.

Det finns många olika saker du kan göra med intervall i VBA (som att välja, kopiera, flytta, redigera, etc.).

Så för att täcka detta ämne kommer jag att dela upp den här självstudien i avsnitt och visa dig hur du arbetar med celler och intervall i Excel VBA med hjälp av exempel.

Låt oss börja.

Alla koder jag nämner i den här självstudien måste placeras i VB Editor. Gå till avsnittet "Var ska du placera VBA -koden" för att veta hur det fungerar.

Om du är intresserad av att lära dig VBA på det enkla sättet, kolla in min Online Excel VBA -utbildning.

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 det.

I de flesta fall är det bättre att inte välja 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 för ditt VBA -lärande och många begrepp som täcks här kommer att användas under hela denna handledning.

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

Välja en enda cell med hjälp av VBA

Om du vill markera en enda cell i det aktiva arket (säg A1) kan du använda koden nedan:

Sub SelectCell () Range ("A1"). Välj End Sub

Ovanstående kod har den obligatoriska delen 'Sub' och 'End Sub' och en kodrad som väljer cell A1.

Område ("A1") berättar VBA adressen för cellen som vi vill hänvisa till.

Välj är en metod för Range -objektet och väljer de celler/intervall som anges i Range -objektet. Cellreferenserna måste ingå i dubbla citattecken.

Denna kod visar ett fel om ett diagramark är ett aktivt blad. Ett diagramblad innehåller diagram och används inte i stor utsträckning. Eftersom den inte har celler/intervall i sig kan ovanstående kod inte välja den och det kommer att visa ett fel.

Observera att eftersom du vill markera cellen i det aktiva arket behöver du bara ange celladressen.

Men om du vill markera cellen i ett annat blad (låt oss säga Sheet2) måste du först aktivera Sheet2 och sedan markera cellen i den.

Sub SelectCell () Worksheets ("Sheet2"). Aktivera Range ("A1"). Välj End Sub

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

Sub SelectCell () Workbooks ("Book2.xlsx"). Arbetsblad ("Sheet2"). Aktivera intervall ("A1"). Välj End Sub 

Observera att när du hänvisar till arbetsböcker måste du använda hela namnet tillsammans med filtillägget (.xlsx i koden ovan). 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 denna handledning hur vi kan använda samma koncept 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 område.

I händelse av ett intervall kan det vara ett fast storleksintervall eller ett variabelt storleksintervall.

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 en variabel storlek har du ingen aning om hur stort intervallet är och du måste använda lite VBA-magi.

Låt oss se hur du gör detta.

Välja ett fixstorleksintervall

Här är koden som väljer intervallet A1: D20.

Sub SelectRange () Område ("A1: D20"). Välj End Sub 

Ett annat sätt att göra detta är att använda koden nedan:

Sub SelectRange () Område ("A1", "D20"). Välj End Sub

Ovanstående kod tar celladressen längst upp till vänster (A1) och den nedre högra celladressen (D20) och väljer hela intervallet. Denna teknik blir användbar när du arbetar med intervall med varierande storlek (som vi kommer att se när egenskapen End täcks senare i den här självstudien).

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

Till exempel skulle koden nedan välja intervallet A1: D20 i Sheet2 -kalkylbladet i Book2 -arbetsboken.

Sub SelectRange () Workbooks ("Book2.xlsx"). Arbetsblad ("Sheet1"). Aktivera intervall ("A1: D20"). Välj End Sub

Tänk om du inte vet hur många rader det finns. Vad händer om du vill markera alla celler som har ett värde i den.

I dessa fall måste du använda metoderna som visas i nästa avsnitt (för att välja intervall med variabel storlek).

Välja ett intervall med varierande storlek

Det finns olika sätt att välja ett cellområde. Metoden du väljer beror på hur data är uppbyggda.

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

Välj Using CurrentRange Property

I de fall du inte vet hur många rader/kolumner som har data kan du använda egenskapen CurrentRange för Range -objektet.

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

Nedan finns koden som väljer den aktuella regionen som innehåller cell A1.

Sub SelectCurrentRegion () Område ("A1"). CurrentRegion.Select End Sub

Ovanstående metod är bra när du har all data som en tabell utan 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 kalkylbladsobjektet.

Välj Using UsedRange Property

UsedRange låter dig hänvisa 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.Select End Sub

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

Välj Använda slutegenskapen

Nu är den här delen verkligen användbar.

Egenskapen Slut låter dig välja den senast fyllda cellen. Detta låter dig efterlikna effekten av nedåt-/uppåtpilen Kontroll eller höger/vänster.

Låt oss försöka förstå detta med hjälp av ett exempel.

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

Problemet här är att data kan förändras och du vet inte hur många celler som fylls. Om du måste göra detta med tangentbordet kan du markera cell A1 och sedan använda Ctrl + nedåtpilen så markerar den senast fyllda cellen i kolumnen.

Låt oss nu se hur du gör detta med VBA. Denna teknik är till nytta när du snabbt vill hoppa till den sista fyllda cellen i en kolumn med variabel storlek

Sub GoToLastFilledCell () Range ("A1"). End (xlDown) .Välj End 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) .Välj End Sub

Vad händer nu om du vill markera hela kolumnen istället för att hoppa till den senast fyllda cellen.

Du kan göra det med koden nedan:

Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown)). Välj End Sub

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

Kom ihåg exemplet ovan där vi valde intervallet A1: D20 med hjälp av följande kodrad:

Område ("A1 ″," D20 ")

Här var A1 den övre vänstra cellen och D20 var den nedre högra cellen i intervallet. Vi kan använda samma logik för att välja intervall med varierande storlek. Men eftersom vi inte vet den exakta adressen till den nedre högra cellen använde vi egenskapen End för att få den.

I intervall ("A1", intervall ("A1"). Slut (xlDown)) hänvisar "A1" till den första cellen och intervall ("A1"). End (xlDown) hänvisar till den sista cellen. Eftersom vi har tillhandahållit båda referenserna väljer Select -metoden alla celler mellan dessa två referenser.

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

Koden nedan markerar alla de fyllda raderna/kolumnerna från cell A1.

Sub SelectFilledCells () Range ("A1", Range ("A1"). End (xlDown) .End (xlToRight)). Välj End Sub

I koden ovan har vi använt Range ("A1"). End (xlDown) .End (xlToRight) för att få referensen till den nedre högra fyllda cellen i datamängden.

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 intervallet när vi har egenskapen CurrentRegion, låt mig berätta skillnaden.

Med egenskapen Slut 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 egenskapen Slut för att välja data utan rubriker (med hjälp av koden nedan).

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

Men CurrentRegion skulle automatiskt välja hela datasetet, inklusive rubrikerna.

Hittills i denna handledning har vi sett hur man hänvisar till en rad celler på 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.

Kopierar en cell

Om du vill kopiera cell A1 och klistra in den i cell D1 skulle koden nedan göra det.

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

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

I ovanstående exempelkod anges destinationen på samma rad som du använder kopieringsmetoden. Om du vill göra din kod ännu mer läsbar kan du använda koden nedan:

Sub CopyCell () Område ("A1"). Kopieringsdestination: = Område ("D1") Avsluta sub

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

Som du kanske redan har märkt kopierar koden ovan cellen utan att markera den. Oavsett var du befinner dig i kalkylbladet, kommer koden att kopiera cell A1 och klistra in den på D1.

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

Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Vill du skriva över befintliga data", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1 ") End If End Sub

Kopiera ett fixstorleksintervall

Om du vill kopiera A1: D20 i J1: M20 kan du använda koden nedan:

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

I målcellen behöver du bara ange adressen till cellen längst upp till vänster. Koden kopierar automatiskt det exakta kopierade intervallet till destinationen.

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

Koden nedan skulle kopiera A1: D20 från det aktiva arket till Sheet2.

Sub CopyRange () Område ("A1: D20"). Kopiera kalkylblad ("Sheet2"). Range ("A1") End Sub

Ovanstående kopierar data från det aktiva bladet. 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 medan du kopierar data.

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

Det som är bra med koden ovan är att oavsett vilket ark som är aktivt kommer det alltid att kopiera data från Sheet1 och klistra in det i Sheet2.

Du kan också kopiera ett namngivet område med dess namn istället för referensen.

Till exempel, om du har ett namngivet intervall som kallas "SalesData", kan du använda koden nedan för att kopiera dessa data till Sheet2.

Sub CopyRange () Område ("SalesData"). Kopiera kalkylblad ("Sheet2"). Range ("A1") End Sub

Om omfattningen av det namngivna intervallet är hela arbetsboken behöver du inte finnas på arket som har det namngivna intervallet för att köra den här koden. Eftersom det angivna intervallet är avsett för arbetsboken kan du komma åt det från vilket blad som helst med denna kod.

Om du har en tabell med namnet Table1 kan du använda koden nedan för att kopiera den till Sheet2.

Sub CopyTable () Range ("Tabell1 [#All]"). Kopiera kalkylblad ("Sheet2"). Range ("A1") End Sub

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

I följande exempel kopierar jag Excel -tabellen (tabell1) till Book2 -arbetsboken.

Sub CopyCurrentRegion () Område ("Table1 [#All]"). Kopiera arbetsböcker ("Book2.xlsx"). Arbetsblad ("Sheet1"). Range ("A1") End Sub

Denna kod fungerar bara om arbetsboken redan är öppen.

Kopiera ett variabelt storleksintervall

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

Men om du inte kan göra det kan du använda CurrentRegion eller End -egenskapen för intervallobjektet.

Koden nedan skulle kopiera den aktuella regionen i det aktiva arket och klistra in den i Sheet2.

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

Om du vill kopiera den första kolumnen i din datauppsättning till den sista fyllda cellen och klistra in den i Sheet2 kan du använda koden nedan:

Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Kopiera kalkylblad ("Sheet2"). Range ("A1") End Sub

Om du vill kopiera såväl rader som kolumner kan du använda koden nedan:

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

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

Tilldela intervall till objektvariabler

Hittills har vi använt hela adressen till cellerna (t.ex. arbetsböcker ("Book2.xlsx"). Arbetsblad ("Sheet1"). Range ("A1")).

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

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

Sub CopyRange () Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets ("Sheet1"). Range ("A1: D20") Set DestinationRange = Worksheets ("Sheet2"). Range ("A1") SourceRange.Copy DestinationRange Avsluta Sub

Vi börjar med att deklarera variablerna som intervallobjekt. Sedan tilldelar vi intervallet till dessa variabler med Set -satsen. När intervallet har tilldelats variabeln kan du helt enkelt använda variabeln.

Ange data i nästa tomma cell (med hjälp av inmatningsrutan)

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

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

Sub EnterData () Dim RefRange As Range Set RefRange = Range ("A1"). End (xlDown) .Offset (1, 0) Set ProductCategory = RefRange.Offset (0, 1) Set Quantity = RefRange.Offset (0, 2 ) Ange belopp = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("produktkategori") Quantity.Value = InputBox ("kvantitet") Amount.Value = InputBox ("Amount") End Sub

Ovanstående kod använder rutan VBA -inmatning för att hämta ingångarna från användaren och matar sedan in ingångarna i de angivna cellerna.

Observera att vi inte använde exakta cellreferenser. Istället har vi använt egenskapen Slut och förskjutning för att hitta den sista tomma cellen och fylla data i den.

Denna kod ä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, kommer du att märka att Excel tillåter det. Du kan använda ett If -villkor för att kontrollera om värdet är numeriskt eller inte och sedan tillåta det i enlighet därmed.

Looping Through Cells / Ranges

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

I det här avsnittet kommer vi att se hur man går igenom en uppsättning celler/rader/kolumner i ett intervall. Detta kan vara användbart när du vill analysera varje cell och utföra en åtgärd baserad på den.

Om du till exempel vill markera var tredje rad i markeringen måste du gå igenom och leta efter radnumret. På samma sätt, om du vill markera alla negativa celler genom att ändra teckensnittsfärgen till röd, måste du gå igenom och analysera varje cellvärde.

Här är koden som går igenom raderna i de markerade cellerna och markerar alternativa rader.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Set Myrange = Val för varje Myrow i Myrange.Rows If Myrow.Row Mod 2 = 0 Sedan Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

Ovanstående kod använder MOD -funktionen för att kontrollera radnumret i urvalet. Om radnumret är jämnt markeras det i cyan färg.

Här är ett annat exempel där koden går igenom varje cell och markerar cellerna som har ett negativt värde i den.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Urval för varje Mycell I Myrange Om Mycell <0 Då Mycell.Interior.Color = vbRed End If Next Mycell End 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 endast för att visa hur looping fungerar med celler och intervall i VBA.

Var ska man lägga VBA -koden

Undrar du vart VBA -koden går i din Excel -arbetsbok?

Excel har en VBA -backend som kallas VBA -editor. Du måste kopiera och klistra in koden i fönstret VB Editor -modulkod.

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

1. Gå till fliken Utvecklare.
2. Klicka på alternativet Visual Basic. Detta öppnar VB -redigeraren i backend.
3. I fönstret Projektutforskare i VB-redigeraren högerklickar du på valfritt objekt för arbetsboken där du vill infoga koden.Om du inte ser Project Explorer går du till fliken View och klickar på Project Explorer.
4. Gå till Infoga och klicka på Modul. Detta kommer att infoga ett modulobjekt för din arbetsbok.
5. Kopiera och klistra in koden i modulfönstret.