“Als ik mijn VBA code rijen laat verwijderen dan duurt dit nogal lang… is hier een oplossing voor?”

Omdat ik deze vraag zo vaak tegen kom op fora, leek het me een goed idee dit eens uitgebreid te bespreken. Dit bleek niet zo eenvoudig… Er zijn namelijk veel verschillende methodes bekend.

Stel, je hebt een werkblad met 100.000 rijen. Daarin wil je gaan controleren bij welke rijen kolom “C” de waarde “x” bevat. Als dit zo is, dan moet die volledige rij verwijderd worden.

Volgende methodes gaan we uitproberen:

• Methode 1: Achterwaarts door alle rijen lopen en rijen één voor één verwijderen
• Methode 2: Screenupdating / Calculation uitgeschakelen
• Methode 3: Achterwaarts door alle rijen lopen met Union
• Methode 4: Autofilter gebruiken
• Methode 5: SpecialCells

We voorzien 4 testbladen waarop we de verschillende methodes zullen testen:

• Blad 1: een werkblad met 8 kolommen en 100.000 gevulde cellen per kolom. We vullen de cellen met willekeurige getallen, tekst en formules. Ik plaats in 30 cellen in kolom “C” de waarde “x”.
• Blad 2: een werkblad met 8 kolommen en 100.000 gevulde cellen per kolom. We vullen de cellen met willekeurige getallen, tekst en formules. Ik plaats in 300 cellen in kolom “C” de waarde “x”.

• Blad 3: een werkblad met 8 kolommen en 100.000 gevulde cellen per kolom. We vullen de cellen met willekeurige getallen, tekst en formules. Ik plaats in 3000 cellen in kolom “C” de waarde “x”.

• Blad 3: een werkblad met 8 kolommen en 100.000 gevulde cellen per kolom. We vullen de cellen met willekeurige getallen, tekst en formules. Ik plaats in 30000 cellen in kolom “C” de waarde “x”.

Methode 1: Achterwaarts door alle rijen lopen

We kunnen Excel over het blad laten lopen, laten controleren of rij “C” een “x” bevat in een bepaalde kolom, deze rij laten schrappen en dan naar de volgende rij te gaan.

Opgelet: om deze code succesvol te laten verlopen moeten we achterwaarts (van beneden naar boven) gaan met onze loop. Zo niet wordt steeds de rij na een geschrapte rij overgeslagen!

Sub Methode1()

Dim i As Long

With ActiveWorkbook.Sheets(1)

    For i = 100000 To 1 step -1

        If .Cells(i, "C") = "x" Then

            .Cells(i, "C").EntireRow.Delete

        End If

    Next i

End With

End Sub

We laten onze code los op onze 4 testbladen en krijgen volgend resultaat:

De 30000 lijnen heb ik niet getest. Deze zal langer dan 20.000 seconden duren uitgaande van de resultaten van de 30, 300 en 3000 lijnen. Hier wordt dus duidelijk dat dit absoluut niet de goede methode is om dit te doen.

Methode 2: Zet Calculation / ScreenUpdating uit!

De meeste tijd gaat verloren als volgt: elke keer als er een lijn geschrapt wordt gaat Excel opnieuw alle formules berekenen. Ook het vernieuwen van het scherm neemt telkens tijd in beslag.

Dit kunnen we voorkomen door de automatische berekening van formules en het vernieuwen van het scherm even uit te zetten, en op het einde van onze code terug aan te zetten:

Sub Methode2()

Dim i As Long

With Application

    .Calculation = xlCalculationManual
    .ScreenUpdating = False

End With

With ActiveWorkbook.Sheets(1)

    For i = 100000 To 1 step -1

        If .Cells(i, "C") = "x" Then

            .Cells(i, "C").EntireRow.Delete
        End If

    Next i

End With

With Application

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True

End With

End Sub

We testen deze code opnieuw op onze testbladen:

Methode 3: Achterwaarts door alle rijen lopen met Union

We gaan proberen dit nog wat te versnellen door tijdens de loop enkel ‘waar te nemen’ welke rijen verwijderd moeten worden. Achteraf zullen we pas de nodige rijen schrappen.

Opgelet! Er is een limiet van 8192 areas wanneer je Union gebruikt: http://support.microsoft.com/default.aspx?scid=kb;en-us;832293. Sinds Excel 2010 is dit probleem opgelost.

Sub Methode3()

Dim i As Long
Dim rng As Range

With ActiveWorkbook.Sheets(1)

    For i = 100000 To 1 step -1

        With .Cells(i, "C")

             If .Value = "x" Then

                If rng Is Nothing Then

                    Set rng = .Cells

                Else

                    Set rng = Application.Union(rng, .Cells)

                End If

             End If

        End With

    Next i

    If Not rng Is Nothing Then rng.EntireRow.Delete

End With

End Sub

Met deze code krijgen we volgende resultaten:

Merk op dat enkel bij het schrappen van 3000 rijen deze methode niet sneller is dan Methode 2!

Methode 4: Autofilter gebruiken

Quentemy herinnerde me eraan dat we ook Autofilter kunnen gebruiken om rijen te schrappen. Deze ingebouwde functionaliteit van Excel kan de lege rijen eruit filteren en dan het geheel ineens schrappen.

Het is niet nodig om een Loop over het werkblad uit te voeren, wat tijdbesparend werkt.

Sub Methode4()

Dim rng As Range

With Application

    .Calculation = xlCalculationManual
    .ScreenUpdating = False

End With

With ActiveWorkbook.Sheets(1)

    .Rows(1).Insert Shift:=xlDown

    .Range("A1:I100000").AutoFilter Field:=3, Criteria1:="x"

    With .AutoFilter.Range

        On Error Resume Next

        Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                  .SpecialCells(xlCellTypeVisible)

        On Error GoTo 0

        If Not rng Is Nothing Then rng.EntireRow.Delete

    End With

    .AutoFilterMode = False

    .Rows(1).EntireRow.Delete

End With

With Application

    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True

End With

End Sub

Zoals verwacht doet deze methode het ook heel goed:

Methode 5: Als de voorwaarde een lege cel is

Bovenstaande code kan je voor allerlei voorwaarden gebruiken. Echter, als de voorwaarde een lege cel is, dan bestaat er nog een andere methode. Excel heeft namelijk een ingebouwde functie die lege cellen opspoort in een bereik, zijnde Specialcells(xlCellTypeBlanks).

Opgelet! Er is een limiet van 8192 areas wanneer je SpecialCells gebruikt: http://support.microsoft.com/default.aspx?scid=kb;en-us;832293. Sinds Excel 2010 is dit probleem opgelost.

Opnieuw: geen Loop uit te voeren, wat tijdbesparend werkt.

Sub Methode5()

ActiveWorkbook.Sheets(1).Range("C:C"). _
    SpecialCells(xlCellTypeBlanks).EntireRow.Delete

On Error GoTo 0

End Sub

Met als resultaat:

Conclusie

Uit mijn testen blijkt dat het niet zo eenvoudig is om te bepalen wat nu de beste manier is. Deze is afhankelijk van heel wat factoren. Het is dus onmogelijk om een methode aan te duiden die in alle gevallen het snelste werkt.

Een volledig overzicht van de looptijden van onze macro’s:

Om het wat overzichelijker te maken heb ik het in een scoretabel gegoten. De snelste per testblad krijgt 5 punten, de tweede snelste krijgt 4 punten, enzovoort.

Wanneer je lege cellen gaat gebruiken als voorwaarde, raad ik aan om methode 5 te gebruiken. Deze komt als koploper uit de tests. Moest je hier tegen het maximum van 8192 stoten, dan kan je kiezen voor Methode 4.

Moet je op een andere waarde gaan controleren, dan valt Methode 5 weg als mogelijkheid, en kunnen we best kiezen voor Methode 4 (Autofilter). Gemiddeld is deze methode vrij snel en we lopen geen gevaar om een limiet te bereiken zoals bij Methode 3 en 5.

Kies je toch liever een andere manier? Wanneer het gaat om weinig rijen, maakt het misschien niet zoveel uit of het nu 1 seconde langer duurt of niet. Vergeet enkel niet om ScreenUpdating en Calculation uit te schakelen.

Met dank aan BSALV, voor zijn waardevolle bijdrage aan dit artikel.