Arbeide med Celler og Områder i Excel VBA (Velge, Kopiere, Flytte, Endre)

januar 2, 2021 admin 0 Comments

Når du arbeider med Excel, mesteparten av tiden er brukt i regnearket området – arbeider med celler og områder.

hvis du ønsker å automatisere arbeidet ditt i Excel ved hjelp av VBA, trenger du å vite hvordan å arbeide 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 merke, kopiere, flytte, redigere, etc.).

Så for å dekke dette emnet, jeg vil bryte denne opplæringen i deler og viser deg hvordan du kan arbeide med celler og områder i Excel VBA ved hjelp av eksempler.,

La oss komme i gang.

Alle koder som jeg nevner i denne opplæringen må være plassert i VB Redaktør. Gå til ‘Hvor du skal Plassere VBA-Kode» – delen for å vite hvordan det fungerer.

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

Denne Opplæringen Dekker:

Velge en Celle / Område i Excel ved hjelp av VBA

for Å arbeide med celler og områder i Excel ved hjelp av VBA, trenger du ikke å velge det.

I de fleste tilfeller, du er bedre ikke å velge celler eller områder (som vi skal se).,

til Tross for dette, er det viktig at du går gjennom denne delen, og forstå hvordan det fungerer. Dette vil være avgjørende i VBA-læring og en masse begreper som dekkes her vil bli brukt i denne veiledningen.

Så la oss starte med et veldig enkelt eksempel.

Velge en Enkelt Celle ved Hjelp av VBA

Hvis du ønsker å velge en enkelt celle i det aktive arket (si A1), så du kan bruke under kode:

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

koden ovenfor har den obligatoriske » Sub » og «End Sub’ en, og en linje med kode som velger celle A1.,

Range(«A1») forteller VBA-adressen til den cellen som vi ønsker å se.

Velg en metode for Utvalg objektet og velger celler/område som er angitt i Området objekt. Cellen referanser må stå i doble anførselstegn.

Denne koden ville vise en feil i tilfelle et diagramark er en aktiv ark. Et diagramark inneholder diagrammer og er ikke mye brukt. Siden det ikke har celler/områder i det, koden ovenfor kan ikke velge det, og vil ende opp med å vise en feil.

Merk at siden du ønsker å velge en celle i det aktive regnearket, du trenger bare å angi cellen adresse.,

Men hvis du ønsker å velge en celle i et annet ark (la oss si Ark2), må du først aktivere Ark2 og deretter velger du cellen i det.

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

du kan også aktivere en arbeidsbok, og deretter aktivere et bestemt regneark i det, og deretter velger du en celle.

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

Merk at når du refererer til arbeidsbøker, du trenger å bruke fullt navn sammen med den file extension (.xlsx i koden ovenfor). I tilfelle arbeidsboken har aldri vært lagret, du don ikke trenger å bruke den file extension.,

Nå, disse eksemplene er ikke veldig nyttig, men du vil se senere i denne tutorial hvordan vi kan bruke de samme begrepene for å kopiere og lime inn celler i Excel (ved hjelp av VBA).

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

I tilfelle av et utvalg, kan det være en fast størrelse utvalg eller en variabel størrelse utvalg.

I en fast størrelse utvalg, vil du vite hvor stort omfanget er, og du kan bruke den nøyaktige størrelsen på dine VBA-kode. Men med en variabel størrelse spekter, du har ingen anelse om hvor stort omfanget er, og du må bruke en liten bit av VBA-magi.

La oss se på hvordan du gjør dette.,

Velge en løsning Størrelse Spekter

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

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

en Annen måte å gjøre dette på er å bruke under kode:

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

koden ovenfor tar den øverste venstre cellen adresse (A1) og nederste høyre celle-adresse (D20) og velger hele området. Denne teknikken blir nyttig når du arbeider med trinnløst store områder (som vi skal se når Slutten eiendommen er dekket senere i denne veiledningen).,

Hvis du ønsker utvalget å skje i en annen arbeidsbok eller et annet regneark, så du trenger ikke å fortelle VBA de eksakte navn på disse objektene.

For eksempel, nedenfor kode ville merk området A1:D20 i Ark2 regneark i den Book2 arbeidsbok.

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

Nå, hva hvis du ikke vet hvor mange rader er det. Hva hvis du ønsker å velge alle cellene som har en verdi i det.

I disse tilfellene, må du bruke metodene er vist i neste avsnitt (på valg av størrelse varierer mellom å spekter).,

Velge en Størrelse varierer mellom å Spekter

Det er forskjellige måter du kan velge et område med celler. Metoden du velger, vil avhenge av hvordan dataene er strukturert.

I denne delen, jeg vil dekke noen nyttige teknikker som er virkelig nyttig når du arbeider med områder i VBA.

Velg ved Hjelp av CurrentRange Eiendel

I tilfeller der du ikke vet hvor mange rader/kolonner har dataene, kan du bruke CurrentRange eiendom Utvalg objekt.

CurrentRange eiendom dekker alle sammenhengende fylt celler i et dataområde.,

Nedenfor er koden som skal velge den aktuelle regionen som holder celle A1.

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

Den ovennevnte metoden er god når du har alle data som en tabell uten noen tomme rader/kolonner i det.

Men i tilfelle du har tomme rader/kolonner i dataene, vil det ikke velg de etter tomme rader/kolonner. I bildet nedenfor, CurrentRegion kode velger data til rad 10 som rad 11 er blank.

I slike tilfeller, kan du ønsker å bruke UsedRange eiendom-regnearkobjekt.,

Velg ved Hjelp av UsedRange Eiendel

UsedRange kan du se noen celler som har blitt endret.

Så under kode vil velge alle brukt cellene i det aktive regnearket.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Merk at i tilfelle du har et langt-off cellen som har blitt brukt, ville det bli vurdert av koden ovenfor og alle cellene til det som brukes celle ville bli valgt.

Velg ved Hjelp av Slutten Eiendel

Nå, denne delen er virkelig nyttig.

Slutten eiendom kan du velge den siste fylt celle., Dette gjør du for å etterligne effekten av Kontroll Ned/pil Opp-tasten eller ctrl Høyre/Venstre-tastene.

La oss prøve å forstå dette med et eksempel.

Tenk deg at du har et dataset som vist nedenfor og du vil raskt merke den siste fylt celler i kolonne A.

problemet her er at data kan endres, og du vet ikke hvor mange celler er fylt. Hvis du har til å gjøre dette ved hjelp av tastaturet, kan du velge celle A1, og deretter bruker du Ctrl + pil Ned-tasten, og det vil velge det siste fylt celle i kolonnen.

la oss Nå se hvordan du kan gjøre dette ved hjelp av VBA., Denne teknikken kommer i hendig når du ønsker å raskt hoppe til den siste fylt celle i et trinnløst-størrelse kolonne

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

koden ovenfor vil hoppe til den sist fylt celle i kolonne A.

på samme måte, kan du bruke Enden(xlToRight) for å hoppe til siste fylt celle i en rad.

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

Nå, hva hvis du vil merke hele kolonnen, i stedet for å hoppe til den siste fylt celle.,

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

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

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

Husk eksempelet over der vi valgte området A1:D20 ved hjelp av følgende kode:

Range(«A1″,»D20»)

Her A1 var den øverste venstre cellen og D20 var den nederste høyre celle i området. Vi kan bruke samme logikk i å velge varierer mellom størrelse varierer., Men siden vi ikke kjenner den eksakte adressen til nederste høyre celle, har vi brukt Slutten eiendom for å få det.

I Utvalg(«A1», Range(«A1»).Slutten(xlDown)), «A1» refererer til den første cellen og Range(«A1»).Slutten(xlDown) henviser til den siste cellen. Siden vi har gitt både referanser, Velg metoden velger alle cellene mellom disse to referanser.

du kan også velge et hele datasettet som har flere rader og kolonner.

nedenfor kode vil velge alle fylt rader/kolonner fra celle A1.,

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

I koden ovenfor, har vi brukt Range(«A1»).Slutten(xlDown).Slutten(xlToRight) for å få henvisning til nederste høyre fylt celle av datasettet.

Forskjellen mellom å Bruke CurrentRegion og End

Hvis du lurer på hvorfor bruke End eiendom for å velge fylt utvalg når vi har CurrentRegion eiendel, la meg fortelle deg forskjellen.

Med Slutten eiendommen, kan du angi start-celle., For eksempel, hvis du har data i A1:D20, men den første raden er overskrifter, kan du bruke Enden eiendom for å velge data uten overskrifter (med koden nedenfor).

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

Men CurrentRegion vil automatisk velge hele datasettet, inkludert overskrifter.

Så langt i denne opplæringen, har vi sett hvordan å referere til et celleområde ved å bruke forskjellige måter.

Nå la oss se på noen måter, hvor vi faktisk kan bruke disse teknikkene for å få litt arbeid.,

Kopiere Celler / Områder ved Hjelp av VBA

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

La oss starte med et enkelt eksempel.

Kopiere Enkelt Celle

Hvis du ønsker å kopiere celle A1 og lim den inn i celle D1, nedenfor kode ville gjøre det.

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

Merk at kopi-metoden for utvalg objekt kopier cellen (akkurat som ctrl +C) og limer det i den oppgitte destinasjonen.,

I eksempelet ovenfor koden, reisemålet, er angitt på samme linje der du bruke Copy-metoden. Hvis du vil gjøre koden din enda mer lesbar, du kan bruke under kode:

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

De ovennevnte koder vil kopiere og lime inn verdien samt formatering/formler i det.

Som du kanskje allerede har merket, er koden ovenfor kopier cellen uten å merke det. Uansett hvor du er på regneark, koden vil kopiere celle A1 og lim den inn på D1.

vær Også oppmerksom på at koden ovenfor vil overskrive alle eksisterende kode i celle D2., Hvis du vil at Excel skal gi deg beskjed hvis det er allerede noe i celle D1 uten å overskrive det, kan du bruke koden nedenfor.

Kopiere et Fikse Størrelse Spekter

Hvis du ønsker å kopiere A1:D20 i J1:M20, du kan bruke under kode:

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

I målcellen, du trenger bare å spesifisere adressen til den øverste venstre cellen. Koden vil automatisk kopiere nøyaktig kopiert utvalg i målet.

Du kan bruke den samme bygg for å kopiere data fra en ark til andre.

nedenfor kode ville kopiere A1:D20 fra det aktive arket til Ark2.,

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

De ovennevnte kopierer data fra det aktive arket. Så sørg for at ark som har data som er det aktive arket før du kjører den koden. For å være sikker, kan du også angi regnearket er navnet mens du kopiere data.

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

Den gode tingen om koden ovenfor er at uansett hvilken ark er aktiv, vil det alltid kopiere data fra Ark1 og lim den inn i Ark2.

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

For eksempel, hvis du har et navngitt område som kalles «SalesData’, kan du bruke koden nedenfor til å kopiere disse dataene til å Ark2.

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

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

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

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

Du kan også kopiere et utvalg til en annen Arbeidsbok.,

I det følgende eksemplet, jeg vil kopiere Excel-tabell (Table1), i den Book2 arbeidsbok.

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

Denne koden vil kun fungere hvis Arbeidsboken allerede er åpen.

Kopiere en Variabel Størrelse Spekter

En måte å kopiere variabel størrelse varierer, er å konvertere disse til navngitte områdene-eller Excel-Tabell og bruke koder som vist i forrige avsnitt.

Men hvis du ikke kan gjøre det, kan du bruke den CurrentRegion eller Slutten eiendom utvalg objekt.

nedenfor kode vil kopiere gjeldende område i det aktive arket og lim den inn i Ark2.,

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

Hvis du ønsker å kopiere den første kolonnen i datasettet til siste fylt celle og lim den inn i Ark2, du kan bruke under kode:

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

Hvis du ønsker å kopiere rader samt kolonner, kan du bruke koden nedenfor:

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

Merk at alle disse kodene ikke velge cellene samtidig som du får utført. Generelt, vil du finne bare en håndfull tilfeller der du faktisk trenger for å velge en celle/utvalg før du arbeider på det.,

Tilordne Områder for å objektvariabler

Så langt, vi har vært ved hjelp av den fullstendige adressen til celler (for eksempel Arbeidsbøker(«Book2.xlsx»).Worksheets(«Ark1»).Range(«A1»)).

for Å gjøre koden mer oversiktlig, kan du tilordne disse områdene for å objektvariabler og deretter bruke disse variablene.

For eksempel, i under-koden, har jeg tilegnet kilde og destinasjon utvalg for å objektvariabler og deretter brukes disse variablene for å kopiere data fra det ene området til det andre.

Vi begynner med å deklarere variabler som Område objekter., Da vi tildele utvalg til disse variablene ved hjelp av Set-setningen. Når området har blitt tilordnet den variabelen, kan du ganske enkelt bruke variabelen.

legge Inn Data i den første Tomme Cellen (med Bruk av Input-Boksen)

Du kan bruke innskrivingsboksane for å tillate brukeren å skrive inn data.

For eksempel, tenk at du har dataene som er angitt nedenfor, og du vil angi salgsrekord, kan du bruke input-boksen i VBA. Ved hjelp av en kode, og vi kan sørge for at det fyller data i den neste tomme raden.,

koden ovenfor bruker VBA-boksen for å få input fra brukeren, og går deretter inn på innganger i den angitte celler.

Merk at vi ikke bruker eksakt cellereferanser. Vi har i stedet brukt Slutten og Offset eiendommen for å finne den siste tom celle, og fyll ut dataene i den.

Denne koden er langt fra brukbare. For eksempel, hvis du skriver inn en tekst-streng når boksen ber om antall eller beløp, vil du legge merke til at Excel tillater det. Du kan bruke en If betingelse for å sjekke om verdien er numerisk eller ikke, og deretter la den deretter.,

Sløyfe Gjennom Celler / Områder

Så langt vi kan ha sett hvordan å velge, kopiere og skrive inn data i celler og områder.

I denne delen vil vi se på hvordan til å gå gjennom et sett av celler/rader/kolonner i et område. Dette kan være nyttig når du ønsker å analysere hver celle og utføre noen handling som er basert på det.

For eksempel, hvis du ønsker å markere hver tredje rad i utvalget, så du trenger til å gå gjennom og sjekke for radnummeret., Tilsvarende, hvis du ønsker å markere alle de negative celler ved å endre font farge til rødt, må du gå gjennom og analysere hver celle verdi.

Her er koden som vil sløyfe gjennom radene i de valgte cellene, og merk alternativ 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

koden ovenfor bruker MOD funksjon for å sjekke rad nummer i utvalget. Hvis radnummeret er enda, det blir uthevet i cyan farge.

Her er et annet eksempel hvor koden går gjennom hver celle og fremhever celler som har en negativ verdi 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

Merk at du kan gjøre det samme ved hjelp av Betinget Formatering (som er dynamisk og en bedre måte å gjøre dette). Dette eksemplet er bare for det formål som viser deg hvordan looping fungerer med celler og områder i VBA.

Hvor skal du Sette VBA-Kode

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

Excel har en VBA-backend kalt VBA-editor. Du må kopiere og lime inn koden i VB Editor-modulen kode-vinduet.

Her er fremgangsmåten for å gjøre dette:

Gå til kategorien Utvikler.,
Klikk på Visual Basic-alternativet. Dette vil åpne VB redaktør i backend.
I Project Explorer-ruten i VB Editor, høyre-klikk på et objekt for arbeidsboken der du vil sette inn koden. Hvis du ikke ser Project Explorer, kan du gå til Vis-fanen og klikk på Project Explorer.
Gå til Sett inn og klikk på Modulen. Dette vil sette inn en modul objekt for arbeidsboken.
Kopier og lim inn koden i-modul-vinduet.,

Du Kan Også Gjerne Følgende Excel-Tutorials:

Arbeide med Regneark ved hjelp av VBA.
Arbeide med Arbeidsbøker ved hjelp av VBA.
Opprette brukerdefinerte Funksjoner i Excel.
For Neste Loop i Excel VBA – A Beginner ‘ s Guide med Eksempler.
Hvordan å Bruke Excel VBA InStr Funksjon (med praktiske EKSEMPLER).
Excel VBA Msgbox.
Hvordan å spille inn en Makro i Excel.
Hvordan å Kjøre en Makro i Excel.
Hvordan å Lage en Add-in i Excel.,
Excel Personlige Makro-Arbeidsbok | Lagre & Bruke Makroer i Alle Arbeidsbøker.
Excel VBA Hendelser – En Enkel (og Komplett) Guide.
Excel VBA Feil Håndtering.
Hvordan å Sortere Data i Excel ved hjelp av VBA (EN Trinn-for-Trinn-Guide).
24 Nyttig Excel Makro Eksempler for VBA-Nybegynnere (Klar til bruk).