Ranges in VBA

Data in Excel wordt opgeslagen in cellen. Deze cellen zijn opgeslagen in rijen en kolommen. De rijen worden aangeduid met getallen en de kolommen worden aangeduid met letters. Het adres van een cell bestaat uit een kolomletter en een rijcijfer(bijvoorbeeld ‘A1’).

Een range binnen VBA Excel is één van de belangrijkste objecten . Hij bestaat uit één of meerdere cellen in je werkblad. In dit artikel laat ik je zien welke methodes er zijn om cellen of ranges te specificeren en om hier dan vervolgens mee te gaan werken.

Selecteren van een cell of ranges in VBA

Selecteren van ranges kun je op meerdere manieren bereiken. Laten we als voorbeeld nemen dat we cel ‘A1’ op het tabblad ‘Blad 1’ een waarde willen toekennen.

ThisWorkbook.Sheets("Blad1").Range("A1") = "Cel gevonden"

Een alternatief voor de .Range  methode is het gebruik van .Cells. Je schrijft deze als volgt:

ThisWorkbook.Sheets("Blad1").Cells(1, 1) = "Cel gevonden"

In de praktijk gebruik ik bijna nooit .Cells . Het enige handige aan .Cells is dat je voor kolomaanduiding een cijfer hebt in plaats van een letter. Als je bijvoorbeeld een For Next loop gebruikt om door kolommen heen te gaan dan moet je met .Range de kolomletters omzetten naar kolomcijfers. Dit is echter niet zo lastig als je gebruik maakt van deze functie.

Indien je meerdere cellen wilt selecteren dan is dit alleen mogelijk via .Range. Stel dat wij nu naast cel ‘A1’ ook cel ‘B1’ willen toevoegen aan de range. De code wordt dan:

ThisWorkbook.Sheets("Blad1").Range("A1:B1") = "range gevonden"

Rijen en kolommen selecteren in VBA

Naast één of meerdere cellen is het ook mogelijk om een gehele rij of kolom te selecteren. Dit kan bijvoorbeeld handig zijn als je een hele rij of kolom wilt verwijderen. Een complete rij verwijderen schrijf je als volgt:

ThisWorkbook.Sheets("Blad1").Rows("1:1").Delete

En op deze manier verwijder je een gehele kolom:

ThisWorkbook.Sheets("Blad1").Columns("B:B").Delete

Naast het verwijderen zul je ook de volgende toepassingen op ranges vaak toepassen:

rng.Clear 'Leegmaken van een range
rng.Copy 'Kopiëren van een range
rng.Sort Key1:=rng.Cells(1, 1), Order1:=xlAscending, Header:=xlNo 'Sorteren van een range

Voor meer informatie over het sorteren van ranges klik HIER.

Current region

Current region is een handig hulpmiddel om een range mee te bepalen. Je hoeft met deze functie één enkele cel mee te geven en de current region kijkt dan vanaf deze positie wanneer hij een lege rij of kolom tegenkomt. De lege rij of kolom bepaalt dan het einde van de range.

ThisWorkbook.Sheets("Blad1").Range("A1").CurrentRegion.Select

De selectie van de bovenstaande regel wordt dan:

Ranges zoeken in VBA met behulp van Range.find

Om een specifieke waarde of tekst in een bereik te vinden:

Dim gevondenCel As Range
Set gevondenCel = rng.Find(What:="Zoektekst", LookIn:=xlValues)
If Not gevondenCel Is Nothing Then
    MsgBox "Gevonden in: " & gevondenCel.Address
End If

Voor meer informatie over de toepassing van de range.find methode klik HIER

Dynamische ranges specificeren in VBA

Ranges moet je altijd dynamisch opzetten. Het is alleen niet nodig als je zeker weet dat de range nooit zal wijzigen. Dit zorgt er namelijk voor dat je niet meer je range in je code hoeft aan te passen, zodra er aanpassingen gedaan worden die je range beïnvloeden. Maar hoe doe je dit precies? Stel dat wij een lijst met namen hebben. Deze wil je nu dynamisch opzetten in je code zodat deze ook goed blijft werken als hier namen aan toegevoegd worden of verwijderd worden.

Je kan de range dynamisch maken in één regel code. Dat doe je op deze manier:

ThisWorkbook.Sheets("Blad1").Range("A1:A" & ThisWorkbook.Sheets("Blad1").Range("A1").End(xlDown).Row).Select

Alhoewel de bovenste manier de kortste manier van schrijven is schrijf ik hem persoonlijk niet op deze manier. De reden hiervoor is dat ik hem zo niet leesbaar vind. Wat ik zelf doe is eerst de eindregel bepalen en deze vastleggen in een variabele. Vervolgens gebruik ik deze variabele dan in mijn range:

eindeData = ThisWorkbook.Sheets("Blad1").Range("A1").End(xlDown).Row
ThisWorkbook.Sheets("Blad1").Range("A1:A" & eindeData).Select

Het voordeel van het apart vastleggen van het einde van je data is dat je deze variabele opnieuw kunt gebruiken als je deze verder in je code nogmaals nodig hebt.

Ik heb je nu een praktijkvoorbeeld laten zien hoe je een range dynamisch kunt maken met behulp van de ‘einde range’ eigenschap. In dit voorbeeld gebruikten wij .end(xlDown), maar je kunt uiteraard ook naar boven .end(xlUp), links .end(xlToLeft) en rechts .end(xlToRight) kijken om een einde te bepalen. Wat deze code eigenlijk voor je doet is hetzelfde als vanuit een bepaalde cel Shift+Ctrl plus pijl voor richting in te drukken. 

Offset toepassen op je Ranges in VBA

Met een offset kun je een relatieve locatie aangeven op basis van je ingevoerde range. Stel dat je bijvoorbeeld In het onderstaande voorbeeld de leeftijd van Piet zoekt.

In dat geval staat de data die je zoekt twee kolommen rechts van de naam Piet. Je kunt zijn leeftijd dan op deze manier via een offset ophalen:

leeftijd = ThisWorkbook.Sheets("Blad1").Range("A2").Offset(, 2)

Bij de offset kun je twee waardes meegeven. Als je enkel een offset op een rij wilt meegeven dan kun je één cijfer invoeren en kun je de komma weglaten. In het bovenstaande voorbeeld gaan we twee kolommen naar rechts dus plaatsen wij het cijfer na de komma. Indien je bijvoorbeeld een offset aangeeft van twee rijen omhoog en twee kolommen naar rechts dan geef je dit op deze manier aan:

leeftijd = ThisWorkbook.Sheets("Blad1").Range("A6").Offset(-2, 2)

Wanneer een offset pas echt tot zijn recht komt is in combinatie met de range.find methode. Bij de voorgaande voorbeelden kon je immers ook gewoon direct kolom C gebruiken in je range i.p.v. twee kolommen naar rechts op te schuiven vanaf kolom A. Dit wordt een lastiger verhaal indien je alleen de naam Piet hebt in het voorgaande voorbeeld, maar je weet niet op welke regel deze naam zich bevindt. Je weet namelijk in kolom C niet waar je op moet zoeken, omdat je de leeftijd niet weet van Piet. Daarom zoek je in kolom A naar de naam en verschuif je vanaf deze regel dan twee kolommen naar rechts.

Ik hoop dat ik jullie nu een eind op weg geholpen heb om zelfstandig te werken met ranges in VBA. Mochten jullie nog vragen hebben, stel ze in de reacties!