Arbejde med Celler og Spænder i Excel VBA (Markere, Kopiere, Flytte, Redigere)

januar 2, 2021 admin 0 Comments

Når du arbejder med Excel, det meste af din tid er brugt i regnearket område – der beskæftiger sig med celler og områder.

og hvis du vil automatisere dit arbejde i E .cel ved hjælp af VBA, skal du vide, hvordan du arbejder med celler og intervaller ved hjælp af VBA.

der er mange forskellige ting, du kan gøre med intervaller i VBA (såsom vælg, Kopier, Flyt, Rediger osv.).

så for at dække dette emne vil jeg bryde denne tutorial i sektioner og vise dig, hvordan du arbejder med celler og intervaller i E .cel VBA ved hjælp af eksempler.,

lad os komme i gang.

alle de koder, jeg nævner i denne tutorial, skal placeres i VB-editoren. Gå til afsnittet’ Hvor skal du placere VBA-koden ‘ for at vide, hvordan det fungerer.

Hvis du er interesseret i at lære VBA på den nemme måde, så tjek min Online e .cel VBA-træning.

Denne Tutorial Dækker:

Valg af en Celle / Række i Excel ved hjælp af VBA

for At arbejde med celler og spænder i Excel ved hjælp af VBA, du behøver ikke at vælge det.

i de fleste tilfælde har du det bedre ikke at vælge celler eller intervaller (som vi vil se).,

På trods af det er det vigtigt, at du gennemgår dette afsnit og forstår, hvordan det fungerer. Dette vil være afgørende i din VBA-læring, og en masse koncepter, der er dækket her, vil blive brugt i hele denne tutorial.

så lad os starte med et meget simpelt eksempel.

Valg af en Enkelt Celle ved Hjælp af VBA

Hvis du ønsker at vælge en enkelt celle i det aktive ark (siger A1), så kan du bruge den nedenstående kode:

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

ovenstående kode har den obligatoriske ” Sub ” og “End Sub’ en del, og en linje kode, der vælger celle A1.,

rækkevidde(“A1”) fortæller VBA adressen på den celle, som vi vil henvise til.

Select er en metode til Range-objektet og vælger de celler / område, der er angivet i Range-objektet. Cellehenvisningerne skal vedlægges i dobbelt citater.

denne kode viser en fejl, hvis et diagramark er et aktivt ark. Et diagramark indeholder diagrammer og bruges ikke i vid udstrækning. Da den ikke har celler/intervaller i den, kan ovenstående kode ikke vælge den og vil ende med at vise en fejl.bemærk, at da du vil vælge cellen i det aktive ark, skal du bare angive celleadressen.,

men hvis du vil vælge cellen i et andet ark (lad os sige Sheet2), skal du først aktivere Sheet2 og derefter vælge cellen i den.

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

På samme måde kan du også aktivere en projektmappe, derefter aktivere et specifikt regneark i det og derefter vælge en celle.

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

Bemærk, at når du henviser til arbejdsbøger, skal du bruge det fulde navn sammen med filtypenavnet (.abovels.i ovenstående kode). Hvis projektmappen aldrig er blevet gemt, behøver du ikke bruge filtypenavnet.,

nu er disse eksempler ikke særlig nyttige, men du vil se senere i denne tutorial, hvordan vi kan bruge de samme koncepter til at kopiere og indsætte celler i E .cel (ved hjælp af VBA).

ligesom vi vælger en celle, kan vi også vælge en rækkevidde.

i tilfælde af et interval kan det være et fast størrelsesområde eller et variabelt størrelsesområde.

i et fast størrelsesområde vil du vide, hvor stort området er, og du kan bruge den nøjagtige størrelse i din VBA-kode. Men med et variabelt størrelsesområde har du ingen ID.om, hvor stort sortimentet er, og du skal bruge en lille smule VBA-magi.

lad os se, hvordan du gør dette.,

valg af et Fi. – størrelsesområde

Her er koden, der vælger området A1:d20.

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

en Anden måde at gøre dette på er ved hjælp af nedenstående kode:

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

ovenstående kode er af den øverste venstre celle-adresse (A1) og nederste højre celle-adresse (D20) og vælger hele området. Denne teknik bliver nyttig, når du arbejder med forskellige størrelser (som vi vil se, hvornår Slutegenskaben er dækket senere i denne tutorial).,

Hvis du ønsker, at markeringen skal ske i en anden projektmappe eller et andet regneark, skal du fortælle VBA de nøjagtige navne på disse objekter.

for eksempel vælger nedenstående kode intervallet A1:d20 i Sheet2-regnearket i Book2-projektmappen.

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

hvad nu hvis du ikke ved, hvor mange rækker der er. Hvad hvis du vil vælge alle de celler, der har en værdi i den.

i disse tilfælde skal du bruge metoderne vist i næste afsnit (ved valg af variabelt størrelsesområde).,

valg af et variabelt størrelsesområde

Der er forskellige måder, du kan vælge en række celler på. Den metode, du vælger, afhænger af, hvordan dataene er struktureret.

i dette afsnit vil jeg dække nogle nyttige teknikker, der er virkelig nyttige, når du arbejder med intervaller i VBA.

Vælg Brug af Currentrange-ejendom

i tilfælde, hvor du ikke ved, hvor mange rækker / kolonner der har dataene, kan du bruge Currentrange-egenskaben for Rækkeobjektet.

egenskaben CurrentRange dækker alle de tilstødende fyldte celler i et dataområde.,

nedenfor er koden, der vælger den aktuelle region, der indeholder celle A1.

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

ovenstående metode er god, når du har alle data som en tabel uden tomme rækker / kolonner i den.

men hvis du har tomme rækker/kolonner i dine data, vælger den ikke dem efter de tomme rækker / kolonner. På billedet herunder vælger CurrentRegion-koden data til række 10, Da række 11 er tom.

i sådanne tilfælde kan du bruge egenskaben UsedRange for Regnearkobjektet.,

Vælg Brug af UsedRange-ejendom

UsedRange giver dig mulighed for at henvise til alle celler, der er blevet ændret.

så nedenstående kode ville vælge alle de brugte celler i det aktive ark.

Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub

Bemærk, at hvis du har en fjern celle, der er blevet brugt, vil den blive overvejet af ovenstående kode, og alle cellerne, indtil den brugte celle ville blive valgt.

Vælg ved hjælp af Slutegenskaben

nu er denne del virkelig nyttig.

Slutegenskaben giver dig mulighed for at vælge den sidst fyldte celle., Dette giver dig mulighed for at efterligne effekten af kontrol ned/Op piletast eller kontrol højre/venstre taster.

lad os prøve at forstå dette ved hjælp af et eksempel.

Antag, at du har et datasæt som vist nedenfor, og du vil hurtigt vælge de sidst fyldte celler i kolonne A.

problemet her er, at data kan ændres, og du ved ikke, hvor mange celler der er fyldt. Hvis du skal gøre dette ved hjælp af tastaturet, kan du vælge celle A1 og derefter bruge kontrol + pil ned-tasten, og den vælger den sidst udfyldte celle i kolonnen.

lad os nu se, hvordan du gør dette ved hjælp af VBA., Denne teknik er praktisk, når du hurtigt vil springe til den sidste celle fyldt i en variabel størrelse kolonne

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

ovenstående kode vil hoppe til det sidste fyldt celle i kolonne A.

Ligeledes kan du bruge Enden(xlToRight) for at hoppe til det sidste fyldt celle i en række.

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

hvad nu hvis du vil vælge hele kolonnen i stedet for at hoppe til den sidst fyldte celle.,

Du kan gøre det ved hjælp af koden nedenfor:

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

i ovenstående kode har vi brugt den første og den sidste reference til cellen, som vi skal vælge. Uanset hvor mange fyldte celler der er, vælger ovenstående kode alle.

husk eksemplet ovenfor, hvor vi valgte området A1:D20 ved at bruge følgende kodelinje:

rækkevidde(“A1″,”D20”)

Her var A1 den øverste venstre celle, og D20 var den nederste højre celle i området. Vi kan bruge den samme logik til at vælge variabelt store intervaller., Men da vi ikke kender den nøjagtige adresse på den nederste højre celle, brugte vi Slutegenskaben til at få den.

inden for rækkevidde(“A1”, rækkevidde(“A1″).Ende (endldo .n)),” A1″henviser til den første celle og rækkevidde (“A1”).Ende(endldo .n) henviser til den sidste celle. Da vi har givet begge referencer, vælger Select-metoden alle celler mellem disse to referencer.

På samme måde kan du også vælge et helt datasæt, der har flere rækker og kolonner.

nedenstående kode vælger alle de udfyldte rækker / kolonner, der starter fra celle A1.,

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

i ovenstående kode har vi brugt rækkevidde(“A1”).Ende(endldo .n).Slut (lltoright) for at få referencen til den nederste højre fyldte celle i datasættet.

forskel mellem at bruge CurrentRegion og End

Hvis du undrer dig over, hvorfor bruge end-egenskaben til at vælge det udfyldte interval, når vi har currentregion-egenskaben, så lad mig fortælle dig forskellen.

Med Slutegenskab kan du angive startcellen., Har dine data i A1: D20, men den første række er overskrifter, kan du bruge slutegenskaben til at vælge dataene uden overskrifterne (ved hjælp af koden nedenfor).

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

men den nuværende Region vælger automatisk hele datasættet, inklusive overskrifterne.

indtil videre i denne tutorial har vi set, hvordan man henviser til en række celler ved hjælp af forskellige måder.lad os nu se nogle måder, hvor vi faktisk kan bruge disse teknikker til at få noget arbejde udført.,

Kopier celler/intervaller ved hjælp af VBA

som jeg nævnte i begyndelsen af denne tutorial, er det ikke nødvendigt at vælge en celle for at udføre handlinger på den. Du vil se i dette afsnit, hvordan du kopierer celler og intervaller uden selv at vælge disse.

lad os starte med et simpelt eksempel.

kopiering af enkeltcelle

Hvis du vil kopiere celle A1 og indsætte den i celle D1, ville nedenstående kode gøre det.

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

Bemærk, at kopimetoden for områdeobjektet kopierer cellen (ligesom Control +C) og indsætter den i den angivne destination.,

i ovenstående eksempelkode er destinationen angivet i den samme linje, hvor du bruger Kopieringsmetoden. Hvis du vil gøre din kode endnu mere læsbar, kan du bruge nedenstående kode:

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

ovenstående koder kopierer og indsætter værdien såvel som formatering/formler i den.

som du måske allerede har bemærket, kopierer ovenstående kode cellen uden at vælge den. Uanset hvor du er på regnearket, kopierer koden celle A1 og indsætter den på D1.

Bemærk også, at ovenstående kode overskriver enhver eksisterende kode i celle D2., Hvis du vil have e .cel til at fortælle dig, om der allerede er noget i celle D1 uden at overskrive det, kan du bruge koden nedenfor.

Kopiering af en Fix Mellemstore Vifte

Hvis du ønsker at kopiere A1:D20 i J1:M20, kan du bruge den nedenstående kode:

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

I destination celle, du skal blot angive adressen på den øverste venstre celle. Koden kopierer automatisk det nøjagtige kopierede interval til destinationen.

Du kan bruge den samme konstruktion til at kopiere data fra et ark til det andet.

nedenstående kode ville kopiere A1:d20 fra det aktive ark til Sheet2.,

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

ovenstående kopierer dataene fra det aktive ark. Så sørg for, at det ark, der har dataene, er det aktive ark, før du kører koden. For at være sikker kan du også angive regnearkets navn, mens du kopierer dataene.

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

det gode ved ovenstående kode er, at uanset hvilket ark der er aktivt, vil det altid kopiere dataene fra Sheet1 og indsætte dem i Sheet2.

Du kan også kopiere et navngivet område ved at bruge dets navn i stedet for referencen.,hvis du for eksempel har et navngivet område kaldet ‘SalesData’, kan du bruge nedenstående kode til at kopiere disse data til Sheet2.

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

Hvis omfanget af det navngivne område er hele projektmappen, behøver du ikke være på det ark, der har det navngivne område for at køre denne kode. Da det navngivne område er scoped for projektmappen, kan du få adgang til det fra ethvert ark ved hjælp af denne kode.

Hvis du har en tabel med navnet Table1, kan du bruge nedenstående kode til at kopiere den til Sheet2.

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

Du kan også kopiere et interval til en anden projektmappe.,

i det følgende eksempel kopierer jeg e .cel-tabellen (Table1) til Book2-projektmappen.

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

denne kode fungerer kun, hvis projektmappen allerede er åben.kopiering af et variabelt størrelsesområde

en måde at kopiere variable størrelsesområder på er at konvertere disse til navngivne intervaller eller e .cel-tabel og bruge koderne som vist i det foregående afsnit.

men hvis du ikke kan gøre det, kan du bruge CurrentRegion eller slutegenskaben for rækkeobjektet.

nedenstående kode ville kopiere det aktuelle område i det aktive ark og indsætte det i Sheet2.,

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

Hvis du ønsker at kopiere den første kolonne i dine data til den sidste celle fyldt og indsætte det i Sheet2, kan du bruge den nedenstående kode:

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

Hvis du ønsker at kopiere rækker, samt kolonner, kan du bruge den nedenstående kode:

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

Bemærk, at alle disse koder ikke skal du markere de celler, samtidig med at få udført. Generelt finder du kun en håndfuld tilfælde, hvor du faktisk skal vælge en celle/rækkevidde, før du arbejder på den.,

tildeling af Intervaller til Objektvariabler

indtil videre har vi brugt den fulde adresse på cellerne (såsom arbejdsbøger(“Book2.XLS””).Regneark (“Sheet1”).Område(“A1”)).

for at gøre din kode mere håndterbar kan du tildele disse intervaller til objektvariabler og derefter bruge disse variabler.i nedenstående kode har jeg for eksempel tildelt kilde-og destinationsområdet til objektvariabler og derefter brugt disse variabler til at kopiere data fra et område til det andet.

Vi starter med at erklære variablerne som Intervalobjekter., Derefter tildeler vi området til disse variabler ved hjælp af Sætsætningen. Når området er blevet tildelt variablen, kan du blot bruge variablen.

Indtast Data i Den næste tomme celle (ved hjælp af indtastningsboks)

Du kan bruge indtastningsfelterne til at give brugeren mulighed for at indtaste dataene.Antag for eksempel, at du har datasættet nedenfor, og du vil indtaste salgsrekorden, du kan bruge indtastningsfeltet i VBA. Ved hjælp af en kode kan vi sikre os, at den udfylder dataene i den næste tomme række.,

ovenstående kode bruger VBA-indtastningsboksen til at hente indgange fra brugeren og indtaster derefter indgangene i de specificerede celler.

Bemærk, at vi ikke brugte nøjagtige cellehenvisninger. I stedet har vi brugt ende-og Offsetegenskaben til at finde den sidste tomme celle og udfylde dataene i den.

denne kode er langt fra brugbar. Indtaster en tekststreng, når indtastningsfeltet beder om mængde eller beløb, vil du bemærke, at e .cel tillader det. Du kan bruge en If-tilstand til at kontrollere, om værdien er numerisk eller ej, og derefter tillade den i overensstemmelse hermed.,

Looping gennem celler/intervaller

indtil videre kan vi have set, hvordan man vælger, kopierer og indtaster dataene i celler og intervaller.

i dette afsnit vil vi se, hvordan man løber gennem et sæt celler/rækker / kolonner i en rækkevidde. Dette kan være nyttigt, når du vil analysere hver celle og udføre en handling baseret på den.vil fremhæve hver tredje række i markeringen, skal du gennemløbe og kontrollere for rækkenummeret., Tilsvarende, hvis du vil fremhæve alle de negative celler ved at ændre skrifttypefarven til rød, skal du gennemløbe og analysere hver celles værdi.

Her er koden, der løber gennem rækkerne i de valgte celler og fremhæver alternative rækker.

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

ovenstående kode bruger MOD-funktionen til at kontrollere rækkenummeret i markeringen. Hvis rækkenummeret er lige, bliver det fremhævet i cyan farve.

Her er et andet eksempel, hvor koden går gennem hver celle og fremhæver de celler, der har en negativ værdi i den.,

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

Bemærk, at du kan gøre det samme ved hjælp af betinget formatering (hvilket er dynamisk og en bedre måde at gøre dette på). Dette eksempel er kun med det formål at vise dig, hvordan looping fungerer med celler og intervaller i VBA.

hvor skal VBA-koden placeres

spekulerer du på, hvor VBA-koden går i din e ?cel-projektmappe?

e .cel har en VBA backend kaldet VBA editor. Du skal kopiere og indsætte koden i VB Editor module code vinduet.

Her er trinnene til at gøre dette:

gå til fanen Udvikler.,
Klik på Visual Basic-indstillingen. Dette vil åbne VB editor i backend.
i projektudforskningsruden i Vb-editoren skal du højreklikke på et objekt for den projektmappe, hvor du vil indsætte koden. Hvis du ikke kan se Project e .plorer, skal du gå til fanen Vis og klikke på Project e .plorer.
gå til Indsæt og klik på modul. Dette vil indsætte et modul objekt til din projektmappe.
Kopier og indsæt koden i modulvinduet.,

Du Kan Også godt Lide Følgende Excel-Tutorials:

Arbejde med Regneark ved hjælp af VBA.
arbejde med arbejdsbøger ved hjælp af VBA.
oprettelse af brugerdefinerede funktioner i E .cel.
til næste løkke i E .cel VBA – en Begyndervejledning med eksempler.
Sådan bruges e .cel VBA InStr-funktion (med praktiske eksempler).
Excel VBA Msgbox.
Sådan optages en makro i E .cel.
Sådan køres en makro i E .cel.
Sådan oprettes en tilføjelse i E .cel.,
E .cel Personal Macro Workorkbook | Gem & brug makroer i alle arbejdsbøger.
E .cel VBA Events – en nem (og komplet) Guide.
E .cel VBA fejlhåndtering.
sådan sorteres Data i E .cel ved hjælp af VBA (en trinvis vejledning).
24 nyttige E .cel-Makroeksempler til VBA-begyndere (klar til brug).