Breid Excel tabellen automatisch uit

Voorbeeldbestanden bij dit artikel:
  • Tabellen uitbreiden
  • Inleiding

    Tabelfunctionaliteit in Excel is zeer handig en neemt een groot aantal (Excel) zorgen weg die je wel zou hebben mochten tabellen niet bestaan. Bijvoorbeeld, formules automatisch doortrekken naar alle rijen in de tabel, leesbare formules schrijven, enz. Ik heb er hier eerder over geblogd. Maar wat ik zelf niet zo prettig vind is hoe je op een makkelijke manier nieuwe rijen aan een tabel kan toevoegen.

    • Ofwel moet je naar de laatste rij en kolom in de tabel gaan en Tab drukken
    • Ofwel gebruik je de muis, rechtermuisklik en "Voeg rijen toe"
    • Ofwel selecteer je een cel in de rij net onder de tabel, in een kolom niet berekend is met een formule, en begin je te typen. Dan zal de tabel uitbreiden.

    Noot: als je de rij met totalen inschakelt voor de tabel, dan vervalt optie 3.

    Dus, kunnen we dit op een intelligentere manier doen ? Ja maar door aangepaste VBA-codes te gebruken. Ik heb dat voor mezelf gedaan omdat het zo handig is, en ik ben zo onbaatzuchtig om de code hier voor mijn regelmatige websitebezoekers weg te geven. Opmerkingen of verbetering zijn steeds welkom per email.

    Mijn oplossing

    Ik gebruik de Worksheet_SelectionChange() gebeurtenis. Dat wordt telkens afgevuurd wanneer een nieuwe selectie gemaakt wordt door de gebruiker: met de muis, keyboard, VBA-code, ... Bij deze gebeurtenis ga ik kijken welke cel of cellen geselecteerd werden. Indien de selectie begint net 1 rij onder een bestaande tabel (zonder een rij tussen te laten) dan zal dit VBA-code uitvoeren die de tabel met 1 rij uitbreidt.

    Voorbeeld: gewoon cel A16 selecteren (zie hierboven) zonder maar iets te typen voegt een nieuwe rij toe aan de tabel. Maar ik laat ook toe om cellen B16, C16, D16 of E16, of combinaties, te selecteren. Het eindresultaat is het ter beschikking hebben van 1 nieuwe rij.

    Kunnen we ook automatisch 5 lijnen toevoegen ? Absoluut! Selecteer cellen A16:A20 en de code zal 5 lijnen toevoegen :-)

    En van de nadelen van de methode met Tab (zie eerder) is dat je altijd naar een nieuwe lijn gaat maar terecht komt in de eerste kolom van de tabel. Dat kan goed zijn maar als de tabel links 3 kolommen bevat met berekende cellen, dan is dat niet echt nuttig en zelfs tijdverlies. Je zou liever willen springen naar bijvoorbeeld de 4de kolom in dat geval, om onmiddellijk data input te doen. Mijn code laat toe om op 3 verschillende manieren om te gaan met de keuze van de juiste kolom:

    1. Optie 1: altijd naar de eerste kolom van de tabel gaan
    2. Optie 2: in dezelfde kolom blijven als waar de gebruiker klikte
    3. Optie 3: naar de eerste lege kolom gaan (als die niet lukt dan nemen we optie 1 - kolom 1 van de tabel)
    Die mogelijkheden zal je zien terugkomen in de onderstaande codes.

    Een Excel werkblad kan vanzelfsprekend meerdere tabellen bevatten. M.a.w. de code moet nagaan onder welke tabel geklikt werd zodat die tabel uitgebreid kan worden. Een lus doorheen de tabellen presenteer ik jullie in wat volgt.

    Aangepaste code


    wordt:

    Kolommen A en E in het bestand worden berekend met de ASELECT functie zodat je de resultaten/getallen in deze kolommen zal zien veranderen bij het herberekenen of openen van het bestand :-)

    Hier is de code voor de Worksheet_SelectionChange gebeurtenis:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    VoegRijenToeAanTabel Target, 3
    End Sub

    Daarna heb ik ook deze code die komt in een normale Module in VBA:

    Sub VoegRijenToeAanTabel(r As Range, Optional iChoiceOfColumn As Integer = 3)
    Dim lo As ListObject Dim sTableName As String Dim oNewRow As ListRow Dim rng As Range Dim i As Long ''voor deze macro kan de selectie niet in een tabel zelf zijn ''anders zou gewoon in de tabel klikken steeds leiden tot extra nieuwe rijen On Error Resume Next sTableName = r.Cells(1).ListObject.Name On Error GoTo 0 If Len(sTableName) > 0 Then Exit Sub End If ''zoek een tabel die de selectie matcht For Each lo In r.Parent.ListObjects On Error Resume Next sTableName = r.Cells(1).Offset(-1).ListObject.Name On Error GoTo 0 If Len(sTableName) > 0 Then ''we hebben een tabel gevonden, laten we nu rijen toevoegen For i = 1 To r.Rows.Count Set oNewRow = lo.ListRows.Add(AlwaysInsert:=True) If i = 1 Then Set rng = oNewRow.Range Next ''selecteer een cel in de eerste nieuwe rij 'we kunnnen: '1. altijd naar de eerste kolom van de tabel gaan '2. in dezelfde kolom blijven als waar de gebruiker klikte '3. naar de eerste lege kolom gaan (als die niet lukt dan nemen we optie 1 - kolom 1 van de tabel) Application.EnableEvents = False Select Case iChoiceOfColumn Case 1: 'naar de eerste kolom van de tabel gaan Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select Case 2: 'in dezelfde kolom blijven als waar de gebruiker klikte Application.Intersect(rng, r.Parent.Columns(r.Column)).Select Case 3: 'naar de eerste lege kolom gaan On Error Resume Next rng.SpecialCells(xlCellTypeBlanks).Cells(1).Select If Err.Number Then Err.Clear Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select End If On Error GoTo 0 End Select Application.EnableEvents = True Exit For End If Next
    End Sub

    Ik gebruik deze code steeds in mijn bestanden omdat ik ze zo handig en tijdsbesparend vind, en in het algemeen, probeer ik steeds tabellen te gebruiken waar mogelijk.

    Enjoy !




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links