Werken met cellen en bereiken in Excel VBA (selecteren, kopiëren, verplaatsen, bewerken)
wanneer u met Excel werkt, wordt het grootste deel van uw tijd doorgebracht in het werkbladgebied – omgaan met cellen en bereiken.
en als u uw werk in Excel wilt automatiseren met VBA, moet u weten hoe u met cellen en bereiken kunt werken met VBA.
er zijn veel verschillende dingen die je kunt doen met bereiken in VBA (zoals selecteren, kopiëren, verplaatsen, bewerken, enz.).
dus om dit onderwerp te behandelen, zal ik deze tutorial in secties breken en u laten zien hoe u met cellen en bereiken in Excel VBA werkt met behulp van voorbeelden.,
laten we beginnen.
als je geïnteresseerd bent in het leren van VBA op de gemakkelijke manier, bekijk dan mijn online Excel VBA Training.
Deze Tutorial omvat:
het selecteren van een cel/bereik in Excel met behulp van VBA
om te werken met cellen en bereiken in Excel met behulp van VBA, hoeft u deze niet te selecteren.
in de meeste gevallen kunt u beter geen cellen of bereiken selecteren (zoals we zullen zien).,
ondanks dat, is het belangrijk dat je door deze sectie gaat en begrijpt hoe het werkt. Dit zal cruciaal zijn in uw VBA-leren en veel concepten die hier worden behandeld zullen tijdens deze tutorial worden gebruikt.
dus laten we beginnen met een heel eenvoudig voorbeeld.
het selecteren van een enkele cel met behulp van VBA
Als u een enkele cel in het actieve werkblad wilt selecteren (bijvoorbeeld A1), dan kunt u de onderstaande code gebruiken:
Sub SelectCell()Range("A1").SelectEnd Sub
de bovenstaande code heeft het verplichte ‘Sub’ en ‘End Sub’ gedeelte, en een regel code die cel A1 selecteert.,
Range (“A1”) vertelt VBA het adres van de cel waarnaar we willen verwijzen.
Select is een methode van het Range-object en selecteert de cellen/bereik die zijn opgegeven in het Range-object. De celverwijzingen moeten worden ingesloten in dubbele aanhalingstekens.
deze code geeft een fout weer in het geval dat een diagramblad een actief blad is. Een grafiekblad bevat grafieken en wordt niet veel gebruikt. Omdat het geen cellen/bereiken bevat, kan de bovenstaande code het niet selecteren en zou er uiteindelijk een fout worden weergegeven.
merk op dat omdat u de cel in het actieve werkblad wilt selecteren, u alleen het celadres hoeft op te geven.,
maar als u de cel in een ander blad wilt selecteren (laten we zeggen Sheet2), moet u eerst Sheet2 activeren en vervolgens de cel erin selecteren.
Sub SelectCell()Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
Op dezelfde manier kunt u ook een werkmap activeren, vervolgens een specifiek werkblad activeren en vervolgens een cel selecteren.
Sub SelectCell()Workbooks("Book2.xlsx").Worksheets("Sheet2").ActivateRange("A1").SelectEnd Sub
merk op dat wanneer u naar werkmappen verwijst, u de volledige naam moet gebruiken samen met de bestandsextensie (.xlsx in de bovenstaande code). In het geval dat de werkmap nooit is opgeslagen, hoeft u de bestandsextensie niet te gebruiken.,
nu zijn deze voorbeelden niet erg nuttig, maar u zult later in deze tutorial zien hoe we dezelfde concepten kunnen gebruiken om cellen in Excel te kopiëren en te plakken (met behulp van VBA).
net zoals we een cel selecteren, kunnen we ook een bereik selecteren.
in het geval van een bereik kan het een vast of variabel groottebereik zijn.
In een vaste groottebereik zou u weten hoe groot het bereik is en u kunt de exacte grootte in uw VBA-code gebruiken. Maar met een variabel bereik heb je geen idee hoe groot het bereik is en moet je een beetje VBA-magie gebruiken.
laten we eens kijken hoe dit te doen.,
een bereik met vaste afmetingen selecteren
Hier is de code die het bereik A1:D20 selecteert.
Sub SelectRange()Range("A1:D20").SelectEnd Sub
een andere manier om dit te doen is door de onderstaande code te gebruiken:
Sub SelectRange()Range("A1", "D20").SelectEnd Sub
de bovenstaande code neemt het celadres linksboven (A1) en het celadres rechtsonder (D20) en selecteert het volledige bereik. Deze techniek wordt handig wanneer u met variabel formaat bereiken werkt (zoals we zullen zien wanneer de eigenschap einde later in deze tutorial wordt behandeld).,
Als u wilt dat de selectie in een andere werkmap of een ander werkblad plaatsvindt, moet u VBA de exacte namen van deze objecten vertellen.
de onderstaande code selecteert bijvoorbeeld het bereik A1: D20 in Sheet2-werkblad in de Book2-werkmap.
Sub SelectRange()Workbooks("Book2.xlsx").Worksheets("Sheet1").ActivateRange("A1:D20").SelectEnd Sub
nu, wat als je niet weet hoeveel rijen er zijn. Wat als u alle cellen wilt selecteren met een waarde erin.
in deze gevallen moet u de methoden gebruiken die in de volgende sectie worden getoond (bij het selecteren van bereik van variabel formaat).,
een bereik met variabel formaat selecteren
er zijn verschillende manieren waarop u een celbereik kunt selecteren. De methode die u kiest zou afhangen van hoe de gegevens zijn gestructureerd.
in deze sectie zal ik enkele nuttige technieken behandelen die echt nuttig zijn wanneer u met bereiken in VBA werkt.
selecteer met de eigenschap CurrentRange
in gevallen waarin u niet weet hoeveel rijen / kolommen de gegevens bevatten, kunt u de eigenschap CurrentRange van het object Range gebruiken.
de eigenschap CurrentRange dekt alle aaneengesloten gevulde cellen in een gegevensbereik.,
Hieronder is de code die de huidige regio selecteert die cel A1 bevat.
Sub SelectCurrentRegion()Range("A1").CurrentRegion.SelectEnd Sub
de bovenstaande methode is goed als u alle gegevens als een tabel hebt zonder lege rijen/kolommen erin.
maar in het geval u lege rijen/kolommen in uw gegevens hebt, zal het niet degene na de lege rijen/kolommen selecteren. In de afbeelding hieronder selecteert de CurrentRegion code gegevens tot rij 10 omdat rij 11 leeg is.
In dergelijke gevallen kunt u de eigenschap UsedRange van het werkbladobject gebruiken.,
Select Using UsedRange Property
met UsedRange kunt u verwijzen naar alle cellen die zijn gewijzigd.
dus de onderstaande code selecteert alle gebruikte cellen in het actieve blad.
Sub SelectUsedRegion()ActiveSheet.UsedRange.SelectEnd Sub
merk op dat in het geval dat u een verre cel hebt die is gebruikt, deze door de bovenstaande code zou worden beschouwd en alle cellen tot die gebruikte cel zouden worden geselecteerd.
selecteer met de eigenschap End
nu is dit deel echt nuttig.
met de eigenschap End kunt u de laatst gevulde cel selecteren., Hiermee kunt u het effect van Control Omlaag/Omhoog pijltoets of Control Rechts/Links toetsen na te bootsen.
laten we proberen dit te begrijpen met behulp van een voorbeeld.
stel dat u een dataset hebt zoals hieronder getoond en dat u snel de laatst gevulde cellen in kolom A wilt selecteren.
het probleem hier is dat gegevens kunnen veranderen en dat u niet weet hoeveel cellen gevuld zijn. Als u dit met het toetsenbord moet doen, kunt u cel A1 selecteren en vervolgens Control + Pijl-omlaag gebruiken en de laatst gevulde cel in de kolom selecteren.
laten we nu eens kijken hoe dit te doen met behulp van VBA., Deze techniek is handig als u snel naar de laatst gevulde cel in een kolom met variabele grootte wilt springen
Sub GoToLastFilledCell()Range("A1").End(xlDown).SelectEnd Sub
de bovenstaande code zou naar de laatst gevulde cel in kolom A.
evenzo kunt u het einde(xlToRight) gebruiken om naar de laatst gevulde cel in een rij te springen.
Sub GoToLastFilledCell()Range("A1").End(xlToRight).SelectEnd Sub
nu, wat als u de hele kolom wilt selecteren in plaats van naar de laatst gevulde cel te springen.,
u kunt dat doen met behulp van de onderstaande code:
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown)).SelectEnd Sub
in de bovenstaande code hebben we de eerste en de laatste referentie van de cel gebruikt die we moeten selecteren. Het maakt niet uit hoeveel gevulde cellen er zijn, de bovenstaande code selecteert alles.
onthoud het voorbeeld hierboven waarin we het bereik A1:D20 selecteerden met behulp van de volgende regel code:
bereik(“A1″,”D20”)
Hier was A1 de cel linksboven en D20 de cel rechtsonder in het bereik. We kunnen dezelfde logica gebruiken bij het selecteren van bereiken met variabel formaat., Maar omdat we het exacte adres van de cel rechtsonder niet weten, gebruikten we de eigenschap End om het te krijgen.
binnen bereik (“A1”, bereik (“A1″).End (xlDown)),” A1″verwijst naar de eerste cel en het bereik (“A1”).End (xlDown) verwijst naar de laatste cel. Omdat we beide referenties hebben verstrekt, selecteert de Select-Methode alle cellen tussen deze twee referenties.
Op dezelfde manier kunt u ook een volledige gegevensset selecteren die meerdere rijen en kolommen heeft.
de onderstaande code selecteert alle gevulde rijen / kolommen vanaf cel A1.,
Sub SelectFilledCells()Range("A1", Range("A1").End(xlDown).End(xlToRight)).SelectEnd Sub
in de bovenstaande code hebben we Range (“A1”) gebruikt.Einde (xlDown).End (xlToRight) om de referentie van de rechtsonder gevulde cel van de dataset te krijgen.
verschil tussen het gebruik van CurrentRegion en End
als u zich afvraagt waarom de eigenschap End gebruiken om het gevulde bereik te selecteren wanneer we de eigenschap CurrentRegion hebben, laat me u het verschil vertellen.
met de eigenschap End kunt u de startcel opgeven., Als u bijvoorbeeld uw gegevens in A1:D20 hebt, maar de eerste rij kopteksten zijn, kunt u de eigenschap einde gebruiken om de gegevens te selecteren zonder de kopteksten (met behulp van de onderstaande code).
Sub SelectFilledCells()Range("A2", Range("A2").End(xlDown).End(xlToRight)).SelectEnd Sub
maar de huidige Regio zou automatisch de volledige dataset selecteren, inclusief de headers.
tot nu toe hebben we in deze tutorial gezien hoe je naar een cellenbereik kunt verwijzen op verschillende manieren.
laten we nu een aantal manieren bekijken waarop we deze technieken daadwerkelijk kunnen gebruiken om wat werk gedaan te krijgen.,
kopieer cellen / bereiken met behulp van VBA
zoals ik aan het begin van deze tutorial al zei, is het selecteren van een cel niet nodig om er acties op uit te voeren. U zult in deze sectie zien hoe u cellen en bereiken kunt kopiëren zonder deze zelfs te selecteren.
laten we beginnen met een eenvoudig voorbeeld.
kopiëren van een enkele cel
Als u cel A1 wilt kopiëren en in cel D1 wilt plakken, zou de onderstaande code het doen.
Sub CopyCell()Range("A1").Copy Range("D1")End Sub
merk op dat de kopieermethode van het range-object de cel kopieert (net als Control +C) en deze in de opgegeven bestemming plakt.,
in de bovenstaande voorbeeldcode wordt de bestemming opgegeven op dezelfde regel waar u de kopieermethode gebruikt. Als u uw code nog leesbaarder wilt maken, kunt u onderstaande code gebruiken:
Sub CopyCell()Range("A1").Copy Destination:=Range("D1")End Sub
de bovenstaande codes kopiëren en plakken de waarde en formatteren/formules erin.
zoals u misschien al hebt gemerkt, kopieert de bovenstaande code de cel zonder deze te selecteren. Ongeacht waar u zich op het werkblad bevindt, de code kopieert cel A1 en plakt deze op D1.
merk ook op dat de bovenstaande code elke bestaande code in cel D2 zou overschrijven., Als u wilt dat Excel u laat weten of er al iets in cel D1 staat zonder het te overschrijven, kunt u de onderstaande code gebruiken.
een bereik met vaste afmetingen kopiëren
Als u A1:D20 in J1:M20 wilt kopiëren, kunt u de onderstaande code gebruiken:
Sub CopyRange()Range("A1:D20").Copy Range("J1")End Sub
In de doelcel, u hoeft alleen het adres van de cel linksboven op te geven. De code zou automatisch het exacte gekopieerde bereik kopiëren naar de bestemming.
u kunt dezelfde constructie gebruiken om gegevens van het ene blad naar het andere te kopiëren.
de onderstaande code zou A1:D20 kopiëren van het actieve werkblad naar Sheet2.,
Sub CopyRange()Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
bovenstaande kopieert de gegevens van het actieve werkblad. Zorg er dus voor dat het blad met de gegevens het actieve blad is voordat u de code uitvoert. Om veilig te zijn, kunt u ook de naam van het werkblad opgeven tijdens het kopiëren van de gegevens.
Sub CopyRange()Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1")End Sub
het goede aan bovenstaande code is dat ongeacht welk blad actief is, het altijd de gegevens van Sheet1 zal kopiëren en in Sheet2 zal plakken.
u kunt ook een benoemde bereik kopiëren door de naam te gebruiken in plaats van de referentie.,
bijvoorbeeld, als u een benoemde bereik genaamd ‘SalesData’ hebt, kunt u de onderstaande code gebruiken om deze gegevens naar Sheet2 te kopiëren.
Sub CopyRange()Range("SalesData").Copy Worksheets("Sheet2").Range("A1")End Sub
als het bereik van het benoemde bereik de volledige werkmap is, hoeft u niet op het blad te staan dat het benoemde bereik heeft om deze code uit te voeren. Omdat het benoemde bereik is afgebakend voor de werkmap, kunt u het openen vanaf elk blad met behulp van deze code.
Als u een tabel hebt met de naam Table1, kunt u de onderstaande code gebruiken om deze naar Sheet2 te kopiëren.
Sub CopyTable()Range("Table1").Copy Worksheets("Sheet2").Range("A1")End Sub
u kunt ook een bereik naar een andere werkmap kopiëren.,
in het volgende voorbeeld kopieer ik de Excel-tabel (Table1) naar de werkmap Book2.
Sub CopyCurrentRegion()Range("Table1").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1")End Sub
deze code werkt alleen als de werkmap al geopend is.
een bereik met variabele grootte kopiëren
een manier om bereiken met variabele grootte te kopiëren is door deze om te zetten in benoemde bereiken of Excel-tabel en de codes te gebruiken zoals getoond in de vorige sectie.
maar als u dat niet kunt, kunt u de currentregion of de eigenschap End van het range-object gebruiken.
de onderstaande code zou de huidige regio in het actieve werkblad kopiëren en in Sheet2 plakken.,
Sub CopyCurrentRegion()Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1")End Sub
Als u de eerste kolom van uw gegevensverzameling tot de laatst gevulde cel wilt kopiëren en deze in Sheet2 wilt plakken, kunt u de onderstaande code gebruiken:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1")End Sub
Als u zowel de rijen als de kolommen wilt kopiëren, kunt u de onderstaande code gebruiken:
Sub CopyCurrentRegion()Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1")End Sub
merk op dat al deze codes de cellen niet selecteren tijdens het uitvoeren. Over het algemeen vindt u slechts een handvol gevallen waarin u daadwerkelijk een cel/bereik moet selecteren voordat u eraan werkt.,
het toewijzen van bereiken aan objectvariabelen
tot nu toe hebben we het volledige adres van de cellen gebruikt (zoals werkmappen (“Book2.xlsx”).Werkbladen (“Sheet1”).Bereik (“A1”)).
om uw code beter beheersbaar te maken, kunt u deze bereiken toewijzen aan objectvariabelen en vervolgens deze variabelen gebruiken.
in de onderstaande code heb ik bijvoorbeeld het bron-en doelbereik toegewezen aan objectvariabelen en deze variabelen vervolgens gebruikt om gegevens van het ene bereik naar het andere te kopiëren.
we beginnen met het declareren van de variabelen als Range-objecten., Vervolgens wijzen we het bereik toe aan deze variabelen met behulp van het set statement. Zodra het bereik is toegewezen aan de variabele, kunt u eenvoudig de variabele gebruiken.
Voer gegevens in de volgende lege cel in (met behulp van invoerveld)
u kunt de invoervelden gebruiken om de gebruiker toe te staan de gegevens in te voeren.
bijvoorbeeld, stel dat u de gegevensset hieronder hebt en u het verkooprecord wilt invoeren, dan kunt u het invoerveld in VBA gebruiken. Met behulp van een code kunnen we ervoor zorgen dat het de gegevens in de volgende lege rij vult.,
de bovenstaande code gebruikt het invoerveld VBA om de ingangen van de gebruiker te krijgen en voert vervolgens de ingangen in de opgegeven cellen in.
merk op dat we geen exacte celverwijzingen hebben gebruikt. In plaats daarvan hebben we de eigenschap End en Offset gebruikt om de laatste lege cel te vinden en de gegevens erin te vullen.
deze code is verre van bruikbaar. Als u bijvoorbeeld een tekstreeks invoert wanneer het invoerveld om hoeveelheid of bedrag vraagt, zult u merken dat Excel dit toestaat. U kunt een If-voorwaarde gebruiken om te controleren of de waarde al dan niet numeriek is en deze vervolgens dienovereenkomstig toestaan.,
lus door cellen/bereiken
tot nu toe hebben we gezien hoe de gegevens in cellen en bereiken moeten worden geselecteerd, gekopieerd en ingevoerd.
in deze sectie zullen we zien hoe je een reeks cellen/rijen/kolommen in een bereik doorloopt. Dit kan handig zijn als u elke cel wilt analyseren en op basis daarvan een actie wilt uitvoeren.
bijvoorbeeld, als u elke derde rij in de selectie wilt markeren, moet u doorlopen en controleren op het rijnummer., Evenzo, als u alle negatieve cellen wilt markeren door de letterkleur in rood te wijzigen, moet u de waarde van elke cel doorlopen en analyseren.
Hier is de code die door de rijen in de geselecteerde cellen loopt en alternatieve rijen markeert.
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
de bovenstaande code gebruikt de MOD-functie om het rijnummer in de selectie te controleren. Als het rijnummer even is, wordt het gemarkeerd in cyaankleur.
Hier is een ander voorbeeld waar de code door elke cel gaat en de cellen benadrukt die een negatieve waarde hebben.,
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 op dat u hetzelfde kunt doen met voorwaardelijke opmaak (wat dynamisch is en een betere manier om dit te doen). Dit voorbeeld is alleen bedoeld om u te laten zien hoe looping werkt met cellen en bereiken in VBA.
waar de VBA-Code plaatsen
vraagt u zich af waar de VBA-code in uw Excel-werkmap naartoe gaat?
Excel heeft een VBA-backend genaamd de VBA-editor. U moet de code kopiëren en plakken in het VB Editor module code venster.
Hier volgen de stappen om dit te doen:
- ga naar het tabblad Ontwikkelaar.,
- klik op de Visual Basic optie. Dit opent de VB-editor in de backend.
- in het deelvenster Projectverkenner in de VB-Editor, klik met de rechtermuisknop op een object voor de werkmap waarin u de code wilt invoegen. Als u de Projectverkenner niet ziet, gaat u naar het tabblad Weergave en klikt u op Projectverkenner.
- ga naar Invoegen en klik op Module. Dit zal een module object invoegen voor uw werkmap.
- kopieer en plak de code in het modulevenster.,
U kunt ook de volgende Excel-Tutorials leuk vinden:
- werken met werkbladen met behulp van VBA.
- werken met werkmappen met behulp van VBA.
- door de gebruiker gedefinieerde functies maken in Excel.
- voor de volgende lus in Excel VBA – een beginnershandleiding met voorbeelden.
- Hoe de Excel VBA InStr-functie te gebruiken (met praktische voorbeelden).
- Excel VBA Msgbox.
- Hoe een Macro in Excel op te nemen.
- Hoe een Macro in Excel uit te voeren.
- Hoe maak je een invoegtoepassing in Excel.,
- Excel Personal Macro Workbook / Save & gebruik macro ‘ s in alle werkmappen.
- Excel VBA Events – een eenvoudige (en Complete) gids.
- Excel VBA foutafhandeling.
- Hoe gegevens in Excel te sorteren met behulp van VBA (een stap-voor-stap handleiding).
- 24 handige Excel Macro voorbeelden voor VBA Beginners (gebruiksklaar).