Arbeide med celler og områder i Excel VBA (Velg, Kopier, Flytt, Rediger)

Når du jobber med Excel, blir mesteparten av tiden din brukt i regnearkområdet - å håndtere celler og områder.

Og hvis du vil automatisere arbeidet ditt i Excel ved hjelp av VBA, må du vite hvordan du arbeider med celler og områder ved hjelp av VBA.

Det er mange forskjellige ting du kan gjøre med områder i VBA (for eksempel velge, kopiere, flytte, redigere, etc.).

Så for å dekke dette emnet, vil jeg dele denne opplæringen i seksjoner og vise deg hvordan du arbeider med celler og områder i Excel VBA ved hjelp av eksempler.

La oss komme i gang.

Alle kodene jeg nevner i denne opplæringen må plasseres i VB Editor. Gå til delen "Hvor skal du sette VBA -koden" for å vite hvordan den fungerer.

Hvis du er interessert i å lære VBA på den enkle måten, sjekk min Online Excel VBA -opplæring.

Velge en celle / område i Excel ved hjelp av VBA

For å jobbe med celler og områder i Excel ved hjelp av VBA, trenger du ikke å velge det.

I de fleste tilfeller er det bedre å ikke velge celler eller områder (som vi vil se).

Til tross for det er det viktig at du går gjennom denne delen og forstår hvordan den fungerer. Dette vil være avgjørende for din VBA -læring, og mange konsepter som dekkes her vil bli brukt gjennom denne opplæringen.

Så la oss starte med et veldig enkelt eksempel.

Velge en enkeltcelle ved hjelp av VBA

Hvis du vil velge en enkelt celle i det aktive arket (si A1), kan du bruke koden nedenfor:

Sub SelectCell () Range ("A1"). Velg End Sub

Koden ovenfor har den obligatoriske delen 'Sub' og 'End Sub', og en kodelinje som velger celle A1.

Område ("A1") forteller VBA adressen til cellen som vi ønsker å referere til.

Å velge er en metode for Range -objektet og velger cellene/området som er angitt i Range -objektet. Cellehenvisningene må være vedlagt i doble anførselstegn.

Denne koden viser en feil hvis et diagramark er et aktivt ark. Et diagramark inneholder diagrammer og er ikke mye brukt. Siden den ikke har celler/områder i seg, kan ikke koden ovenfor velge den og vil ende opp med å vise en feil.

Vær oppmerksom på at siden du vil velge cellen i det aktive arket, trenger du bare å spesifisere celleadressen.

Men hvis du vil velge cellen i et annet ark (la oss si Sheet2), må du først aktivere Sheet2 og deretter velge cellen i den.

Sub SelectCell () Regneark ("Sheet2"). Aktiver område ("A1"). Velg End Sub

På samme måte kan du også aktivere en arbeidsbok, deretter aktivere et bestemt regneark i den og deretter velge en celle.

Sub SelectCell () Workbooks ("Book2.xlsx"). Regneark ("Sheet2"). Aktiver område ("A1"). Velg End Sub 

Vær oppmerksom på at når du refererer til arbeidsbøker, må du bruke hele navnet sammen med filtypen (.xlsx i koden ovenfor). Hvis arbeidsboken aldri har blitt lagret, trenger du ikke å bruke filtypen.

Disse eksemplene er ikke veldig nyttige, men du vil se senere i denne opplæringen hvordan vi kan bruke de samme konseptene til å kopiere og lime inn celler i Excel (ved hjelp av VBA).

Akkurat som vi velger en celle, kan vi også velge et område.

I tilfelle av et område kan det være et fast størrelsesområde eller et variabelt størrelsesområde.

I et fast størrelsesområde vet du hvor stort området er, og du kan bruke den eksakte størrelsen i VBA -koden. Men med en variabel størrelse har du ingen anelse om hvor stort området er, og du må bruke litt VBA-magi.

La oss se hvordan du gjør dette.

Velge et fixstørrelsesområde

Her er koden som vil velge området A1: D20.

Sub SelectRange () Område ("A1: D20"). Velg End Sub 

En annen måte å gjøre dette på er å bruke koden nedenfor:

Sub SelectRange () Område ("A1", "D20"). Velg End Sub

Koden ovenfor tar celleadressen øverst til venstre (A1) og den nederste høyre celleadressen (D20) og velger hele området. Denne teknikken blir nyttig når du jobber med områder med variabel størrelse (som vi vil se når End -egenskapen dekkes senere i denne opplæringen).

Hvis du vil at utvalget skal skje i en annen arbeidsbok eller et annet regneark, må du fortelle VBA de eksakte navnene på disse objektene.

For eksempel vil koden nedenfor velge området A1: D20 i Sheet2 -regnearket i Book2 -arbeidsboken.

Sub SelectRange () Workbooks ("Book2.xlsx"). Regneark ("Sheet1"). Aktiver Range ("A1: D20"). Velg End Sub

Hva om du ikke vet hvor mange rader det er. Hva om du vil velge alle cellene som har en verdi i den.

I disse tilfellene må du bruke metodene vist i neste avsnitt (om valg av område med variabel størrelse).

Velge en variabel størrelse

Det er forskjellige måter du kan velge et celleområde på. Metoden du velger vil avhenge av hvordan dataene er strukturert.

I denne delen vil jeg dekke noen nyttige teknikker som er veldig nyttige når du jobber med områder i VBA.

Velg Using CurrentRange Property

I tilfeller der du ikke vet hvor mange rader/kolonner som har dataene, kan du bruke CurrentRange -egenskapen til Range -objektet.

Egenskapen CurrentRange dekker alle sammenhengende fylte celler i et dataområde.

Nedenfor er koden som vil velge gjeldende region som inneholder celle A1.

Sub SelectCurrentRegion () Range ("A1"). CurrentRegion. Velg End Sub

Metoden ovenfor er god når du har alle data som en tabell uten tomme rader/kolonner i den.

Men hvis du har tomme rader/kolonner i dataene dine, vil den ikke velge dem etter de tomme radene/kolonnene. På bildet nedenfor velger CurrentRegion -koden data til rad 10 da rad 11 er tom.

I slike tilfeller kan det være lurt å bruke UsedRange -egenskapen til regnearkobjektet.

Velg Using UsedRange Property

UsedRange lar deg referere til alle cellene som har blitt endret.

Så koden nedenfor ville velge alle brukte celler i det aktive arket.

Sub SelectUsedRegion () ActiveSheet.UsedRange.Select End Sub

Vær oppmerksom på at hvis du har en fjerntliggende celle som har blitt brukt, vil den bli vurdert av koden ovenfor og alle cellene til den brukte cellen ville bli valgt.

Velg Bruke sluttegenskapen

Denne delen er virkelig nyttig.

End -egenskapen lar deg velge den siste fylte cellen. Dette lar deg etterligne effekten av piltastene ned/ned eller kontroll høyre/venstre.

La oss prøve å forstå dette ved å bruke et eksempel.

Anta at du har et datasett som vist nedenfor, og du vil raskt velge de sist fylte cellene i kolonne A.

Problemet her er at data kan endres, og du vet ikke hvor mange celler som er fylt. Hvis du må gjøre dette ved hjelp av tastaturet, kan du velge celle A1, og deretter bruke Ctrl + pil ned, og den vil velge den siste fylte cellen i kolonnen.

La oss nå se hvordan du gjør dette ved hjelp av VBA. Denne teknikken er nyttig når du raskt vil hoppe til den siste fylte cellen i en kolonne med variabel størrelse

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

Koden ovenfor vil hoppe til den siste fylte cellen i kolonne A.

På samme måte kan du bruke End (xlToRight) til å hoppe til den siste fylte cellen på rad.

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

Hva om du vil velge hele kolonnen i stedet for å hoppe til den siste fylte cellen.

Du kan gjøre det ved å bruke koden nedenfor:

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

I koden ovenfor har vi brukt den første og den siste referansen til cellen som vi må velge. Uansett hvor mange fylte celler det er, vil koden ovenfor velge alle.

Husk eksemplet ovenfor der vi valgte området A1: D20 ved å bruke følgende kodelinje:

Rekkevidde ("A1 ″," D20 ")

Her var A1 øverst til venstre celle og D20 var cellen nederst til høyre i området. Vi kan bruke den samme logikken for å velge områder med variabel størrelse. Men siden vi ikke vet den eksakte adressen til cellen nederst til høyre, brukte vi End-egenskapen for å få den.

I Range ("A1", Range ("A1"). End (xlDown)) refererer "A1" til den første cellen og Range ("A1"). End (xlDown) refererer til den siste cellen. Siden vi har gitt begge referansene, velger Select -metoden alle cellene mellom disse to referansene.

På samme måte kan du også velge et helt datasett som har flere rader og kolonner.

Koden nedenfor vil velge alle de fylte radene/kolonnene som starter fra celle A1.

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

I koden ovenfor har vi brukt Range ("A1"). End (xlDown) .End (xlToRight) for å få referansen til cellen nederst til høyre i datasettet.

Forskjellen mellom å bruke CurrentRegion og End

Hvis du lurer på hvorfor du bruker End -egenskapen til å velge det fylte området når vi har CurrentRegion -egenskapen, la meg fortelle deg forskjellen.

Med End -egenskapen kan du angi startcellen. For eksempel, hvis du har dataene dine i A1: D20, men den første raden er overskrifter, kan du bruke End -egenskapen til å velge dataene uten overskriftene (ved hjelp av koden nedenfor).

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

Men CurrentRegion ville automatisk velge hele datasettet, inkludert overskriftene.

Så langt i denne opplæringen har vi sett hvordan vi kan referere til en rekke celler på forskjellige måter.

La oss nå se noen måter hvor vi faktisk kan bruke disse teknikkene for å få gjort noe arbeid.

Kopier celler / områder ved hjelp av VBA

Som jeg nevnte i begynnelsen av denne opplæringen, er det ikke nødvendig å velge en celle for å utføre handlinger på den. Du vil se i denne delen hvordan du kopierer celler og områder uten å velge disse.

La oss starte med et enkelt eksempel.

Kopiere enkeltcelle

Hvis du vil kopiere celle A1 og lime den inn i celle D1, vil koden nedenfor gjøre det.

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

Vær oppmerksom på at kopimetoden for områdeobjektet kopierer cellen (akkurat som Control +C) og limer den inn i den angitte destinasjonen.

I eksempelkoden ovenfor er destinasjonen spesifisert på samme linje som du bruker kopieringsmetoden. Hvis du vil gjøre koden din enda mer lesbar, kan du bruke koden nedenfor:

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

Kodene ovenfor vil kopiere og lime inn verdien samt formatering/formler i den.

Som du kanskje allerede har lagt merke til, kopierer koden ovenfor cellen uten å velge den. Uansett hvor du er i regnearket, vil koden kopiere celle A1 og lime den inn på D1.

Vær også oppmerksom på at koden ovenfor ville overskrive enhver eksisterende kode i celle D2. Hvis du vil at Excel skal gi deg beskjed om det allerede er noe i celle D1 uten å overskrive det, kan du bruke koden nedenfor.

Sub CopyCell () If Range ("D1") "" Then Response = MsgBox ("Vil du overskrive eksisterende data", vbYesNo) End If If Response = vbYes Then Range ("A1"). Copy Range ("D1 ") End If End Sub

Kopiere et fixstørrelsesområde

Hvis du vil kopiere A1: D20 i J1: M20, kan du bruke koden nedenfor:

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

I målcellen trenger du bare å spesifisere adressen til cellen øverst til venstre. Koden vil automatisk kopiere det eksakte kopierte området til destinasjonen.

Du kan bruke den samme konstruksjonen til å kopiere data fra det ene arket til det andre.

Koden nedenfor vil kopiere A1: D20 fra det aktive arket til Ark2.

Sub CopyRange () Område ("A1: D20"). Kopier regneark ("Sheet2"). Range ("A1") End Sub

Ovennevnte kopierer dataene fra det aktive arket. Så sørg for at arket som har dataene er det aktive arket før du kjører koden. For å være trygg kan du også spesifisere navnet på regnearket mens du kopierer dataene.

Sub CopyRange () Regneark ("Ark1"). Område ("A1: D20"). Kopier regneark ("Ark2"). Område ("A1") Slutt Sub

Det gode med koden ovenfor er at uansett hvilket ark som er aktivt, vil det alltid kopiere dataene fra Sheet1 og lime det inn i Sheet2.

Du kan også kopiere et navngitt område ved å bruke navnet i stedet for referansen.

For eksempel, hvis du har et navngitt område kalt 'SalesData', kan du bruke koden nedenfor for å kopiere disse dataene til Sheet2.

Sub CopyRange () Område ("SalesData"). Kopier regneark ("Sheet2"). Range ("A1") End Sub

Hvis omfanget av det navngitte området er hele arbeidsboken, trenger du ikke å være på arket som har det navngitte området for å kjøre denne koden. Siden det navngitte området er omfattet av arbeidsboken, kan du få tilgang til det fra et hvilket som helst ark som bruker denne koden.

Hvis du har en tabell med navnet Table1, kan du bruke koden nedenfor for å kopiere den til Sheet2.

Sub CopyTable () Range ("Table1 [#All]"). Kopier regneark ("Sheet2"). Range ("A1") End Sub

Du kan også kopiere et område til en annen arbeidsbok.

I det følgende eksemplet kopierer jeg Excel -tabellen (tabell1) til Book2 -arbeidsboken.

Sub CopyCurrentRegion () Område ("Table1 [#All]"). Kopier arbeidsbøker ("Book2.xlsx"). Regneark ("Sheet1"). Range ("A1") End Sub

Denne koden fungerer bare hvis arbeidsboken allerede er åpen.

Kopiere et variabelt størrelsesområde

En måte å kopiere områder med variabel størrelse på er å konvertere disse til navngitte områder eller Excel -tabell og bruke kodene som vist i forrige seksjon.

Men hvis du ikke kan gjøre det, kan du bruke CurrentRegion eller End -egenskapen til områdeobjektet.

Koden nedenfor vil kopiere gjeldende region i det aktive arket og lime den inn i Sheet2.

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

Hvis du vil kopiere den første kolonnen i datasettet til den siste fylte cellen og lime den inn i Sheet2, kan du bruke koden nedenfor:

Sub CopyCurrentRegion () Range ("A1", Range ("A1"). End (xlDown)). Kopier regneark ("Sheet2"). Range ("A1") End Sub

Hvis du vil kopiere radene og kolonnene, kan du bruke koden nedenfor:

Sub CopyCurrentRegion () Område ("A1", Område ("A1"). Slutt (xlDown) .End (xlToRight)). Kopier regneark ("Sheet2"). Range ("A1") End Sub

Vær oppmerksom på at alle disse kodene ikke velger cellene mens de kjøres. Generelt finner du bare en håndfull tilfeller der du faktisk trenger å velge en celle/område før du arbeider med den.

Tilordne områder til objektvariabler

Så langt har vi brukt hele adressen til cellene (for eksempel arbeidsbøker ("Book2.xlsx"). Regneark ("Sheet1"). Range ("A1")).

For å gjøre koden mer håndterbar, kan du tilordne disse områdene til objektvariabler og deretter bruke disse variablene.

For eksempel i koden nedenfor har jeg tilordnet kilde- og destinasjonsområdet til objektvariabler og deretter brukt disse variablene til å kopiere data fra det ene området til det andre.

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 Slutt Sub

Vi starter med å deklarere variablene som Range -objekter. Deretter tilordner vi området til disse variablene ved hjelp av Set -setningen. Når området er tilordnet variabelen, kan du ganske enkelt bruke variabelen.

Skriv inn data i neste tomme celle (ved hjelp av inndataboks)

Du kan bruke Input -boksene for å la brukeren legge inn dataene.

Anta for eksempel at du har datasettet nedenfor og du vil angi salgsrekorden, du kan bruke inndataboksen i VBA. Ved å bruke en kode kan vi sikre at den fyller ut dataene i den neste tomme raden.

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 ) Angi beløp = RefRange.Offset (0, 3) RefRange.Value = RefRange.Offset (-1, 0) .Value + 1 ProductCategory.Value = InputBox ("Produktkategori") Quantity.Value = InputBox ("Mengde") Amount.Value = InputBox ("Amount") End Sub

Koden ovenfor bruker VBA Input -boksen til å hente inngangene fra brukeren, og legger deretter inngangene inn i de spesifiserte cellene.

Vær oppmerksom på at vi ikke brukte eksakte cellereferanser. I stedet har vi brukt End and Offset -egenskapen til å finne den siste tomme cellen og fylle dataene i den.

Denne koden er langt fra brukbar. For eksempel, hvis du skriver inn en tekststreng når innboksen ber om mengde eller mengde, vil du legge merke til at Excel tillater det. Du kan bruke en If -betingelse for å kontrollere om verdien er numerisk eller ikke, og deretter tillate den deretter.

Looping gjennom celler / områder

Så langt kan vi ha sett hvordan vi velger, kopierer og skriver inn dataene i celler og områder.

I denne delen vil vi se hvordan du går gjennom et sett med celler/rader/kolonner i et område. Dette kan være nyttig når du vil analysere hver celle og utføre noen handlinger basert på den.

For eksempel, hvis du vil markere hver tredje rad i utvalget, må du gå gjennom og se etter radnummeret. På samme måte, hvis du vil markere alle de negative cellene ved å endre skriftfargen til rød, må du gå gjennom og analysere hver celles verdi.

Her er koden som vil gå gjennom radene i de valgte cellene og markere alternative rader.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Myrow As Range Sett Myrange = Valg for hver Myrow i Myrange.Rader Hvis Myrow.Row Mod 2 = 0 Deretter Myrow.Interior.Color = vbCyan End If Next Myrow End Sub

Koden ovenfor bruker MOD -funksjonen for å kontrollere radnummeret i utvalget. Hvis radnummeret er jevnt, blir det markert med cyanfarge.

Her er et annet eksempel hvor koden går gjennom hver celle og markerer cellene som har en negativ verdi i den.

Sub HighlightAlternateRows () Dim Myrange As Range Dim Mycell As Range Set Myrange = Valg for hver Mycell I Myrange Hvis Mycell <0 Da Mycell.Interior.Color = vbRed End If Next Mycell End Sub

Vær oppmerksom på at du kan gjøre det samme ved å bruke betinget formatering (som er dynamisk og en bedre måte å gjøre dette på). Dette eksemplet er bare for å vise deg hvordan looping fungerer med celler og områder i VBA.

Hvor å plassere VBA -koden

Lurer du på hvor VBA -koden går i Excel -arbeidsboken din?

Excel har en VBA -backend kalt VBA -redaktøren. Du må kopiere og lime inn koden i VB Editor -modulvinduet.

Her er trinnene for å gjøre dette:

1. Gå til kategorien Utvikler.
2. Klikk på Visual Basic -alternativet. Dette vil åpne VB -redaktøren i backend.
3. I Project Explorer-ruten i VB Editor, høyreklikker du på et hvilket som helst objekt for arbeidsboken der du vil sette inn koden.Hvis du ikke ser Project Explorer, går du til kategorien View og klikker på Project Explorer.
4. Gå til Sett inn og klikk på Modul. Dette vil sette inn et modulobjekt for arbeidsboken din.
5. Kopier og lim inn koden i modulvinduet.