8 december 2011

Regels verwijderen uit Excel

Wanneer ik een grote hoeveelheid rijen in Excel heb waarvan ik slechts een kleine selectie nodig heb, dan voeg ik meestal een kolom getiteld "Filter" toe waarin ik de rijen markeer die ik nodig heb. In die kolom zet ik dan deze formule:

    =IF(<conditie>;1;0)

Een bijkomend voordeel is dat, als je de hele kolom selecteert, je direct de som ziet van de rijen die in het filter zitten. Ook kan je "Autofilter" activeren via [Data]->[Filter]->[Autofilter], waarna je de kolomfilter via de dropdown op 1 zet en precies de gewenste dataset krijgt.

As je vervolgens de rijen wilt copy-pasten naar bijvoorbeeld MiniTab, dan lukt dit meestal door het autofilter er op te zetten volgens bovenstaande uitleg en de rijen te selecteren. Echter, wanneer de dataset te groot is dan kan je de melding "verwijzing te complex" (Engels: "Reference is too complex") krijgen. Hierna wordt alsnog(!) de gehele dataset naar het clipboard gestuurd. Dit kan erg irritant zijn.

Om toch de benodigde data te verkrijgen zou je de rijen kunnen verwijderen die uit het filter vallen. Op die manier houd je alleen de rijen over die je nodig hebt. Hiervoor heb ik een kleine macro geschreven die alle rijen verwijdert die de waarde 0 hebben staan in de eerste cel (dit is de kolom waarin ik eerder genoemd filter heb toegevoegd).

    Dim L as Long
    For L = Range("A65536").End(xlUp).Row To 2 Step -1
      If Cells(L, 1) = 0 Then
        Rows(L).Delete
      End If
    Next

De macro loopt van beneden naar boven in plaats van andersom omdat je anders steeds de rij overslaat die onmiddelijk na een te verwijderen rij komt. Wanneer je meer rijen hebt dan 65536 dan zal je hier de waarde van de laatste rij zelf even neer kunnen zetten.

In Excel2007/2010 kan de macro gemakkelijk toegevoegd worden door een button toe te voegen op de worksheet via [Developer]->[Insert]->[Button], waarna je [New] selecteert in de pop-up. Daarna voeg je bovenstaande code in. Voor oudere versies van Excel kan je de macro toevoegen via [Extra]->[Macro]->[Visual Basic Editor].

Als je alles goed hebt gedaan en je runt de macro dan kan het nog even duren voordat alle rijen verwijderd zijn. Maar het zal in elk geval stukken sneller gaan dan alles handmatig nalopen en verwijderen.

Let op! Als je de uitgefilterde rijen later nog nodig hebt, vergeet dan niet om de hele dataset separaat te saven vóórdat je de macro runt; er is namelijk geen weg terug!