Het toevoegen en verwijderen van rijen maakt deel uit van de dagelijkse veelvoorkomende taken bij het werken met Excel.
Hoewel u dit eenvoudig vanuit het werkblad zelf kunt doen, wilt u soms de VBA-route gebruiken om rijen in Excel te verwijderen. Dit kunnen zijn het verwijderen van een specifieke rij, meerdere rijen in de selectie, het verwijderen van alternatieve rijen of rijen die een specifieke waarde bevatten.
In deze zelfstudie laat ik u zien hoe u rijen in Excel verwijdert met VBA (meerdere scenario's).
Dus laten we beginnen!
Een hele rij verwijderen met VBA
Als u een hele rij in Excel wilt verwijderen met VBA, moet u de methode EntireRow.Delete gebruiken.
Als u bijvoorbeeld de hele eerste rij in een werkblad wilt verwijderen, kunt u de onderstaande code gebruiken:
Sub DeleteEntireRow() Rows(1).EntireRow.Delete End Sub
De bovenstaande code specificeert eerst de rij die moet worden verwijderd (wat wordt gedaan door het getal tussen haakjes op te geven) en gebruikt vervolgens de methode EntireRow.Delete om deze te verwijderen.
U kunt ook meerdere rijen verwijderen door deze rijen in de code op te geven.
De onderstaande code verwijdert bijvoorbeeld rij 1, 5 en 9 in het werkblad:
Sub DeleteEntireRow() Rows(9).EntireRow.Delete Rows(5).EntireRow.Delete Rows(1).EntireRow.Delete End Sub
De bovenstaande code gebruikt dezelfde logica, waarbij het de rijnummers specificeert en Excel deze rijen één voor één verwijdert.
BELANGRIJK: wanneer u rijen verwijdert met iets dat lijkt op de bovenstaande code, vergeet dan niet om vanaf de onderkant te beginnen met verwijderen en vervolgens naar boven te gaan. Als u bijvoorbeeld bovenaan begint en rij 1 eerst verwijdert, worden alle rijen eronder een rij naar boven verschoven en is de nummering uit (aangezien rij 5 rij 4 wordt, enzovoort)Alle rijen in de selectie verwijderen
Als u alle rijen in een geselecteerd cellenbereik wilt verwijderen, kunt u de onderstaande VBA-macrocode gebruiken:
Sub DeleteEntireRow() Selection.EntireRow.Delete End Sub
De bovenstaande code is van toepassing op de methode EntireRow.Delete op de gehele selectie.
Verwijder alternatieve rijen (of verwijder elke derde/vierde/nde rij)
Soms krijgt u een gegevensdump waarbij elke tweede rij (of derde, vierde of N-de rij) nutteloos is en moet worden verwijderd.
Ik werkte vroeger met financiële gegevens waarbij elke tweede rij leeg was en moest worden verwijderd.
Dit is het soort scenario waarin VBA echt uitblinkt.
Hieronder staat de VBA-code die alle rijen in de selectie doorloopt en elke tweede rij verwijdert:
Sub DeleteAlternateRows() RCount = Selection.Rows.Count For i = RCount tot 1 Stap -2 Selection.Rows(i).EntireRow.Delete Next i End Sub
Laat me uitleggen hoe deze VBA-code werkt.
Ten eerste heb ik een variabele gebruikt Rtel om het totale aantal rijen in de selectie te krijgen.
Vervolgens heb ik een For Next-lus gebruikt om dit zo vaak uit te voeren als er rijen zijn. Als er bijvoorbeeld 12 rijen zijn, loopt deze lus van 12 naar 1 (d.w.z. 12 keer). Het is belangrijk om dit uit te voeren van de laatste rij in de selectie naar de eerste, omdat we niet willen dat de rijnummers veranderen wanneer een rij wordt verwijderd.
Stap -2 wordt ook gebruikt omdat we om de andere rij moeten verwijderen (van onder naar boven). Als u elke derde rij wilt verwijderen, kunt u -3 gebruiken.
Binnen de VBA-lus heb ik de methode Selection.Rows(i).EntireRow.Delete gebruikt om elke alternatieve rij te verwijderen.
Lege rijen verwijderen met VBA
U kunt ook de methode EntireRow.Delete gebruiken om alle lege rijen te verwijderen.
Hieronder staat de VBA-code die lege cellen in de geselecteerde dataset selecteert en de hele rij verwijdert.
Sub DeleteBlankRows() Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete End Sub
De bovenstaande code gebruikt de eigenschap SpecialCells om alle lege cellen te selecteren en te verwijderen. Dit is dezelfde methode waarmee we het dialoogvenster 'Ga naar speciaal' ook kunnen gebruiken om alle lege cellen te selecteren.
Zodra deze lege cellen zijn geïdentificeerd met behulp van SpecialCell, worden deze vervolgens verwijderd met behulp van de methode EntireRow.Delete.
Notes: Deze methode selecteert cellen die leeg zijn en controleert niet of de hele rij leeg is of niet. Dus als een cel leeg is in een rij, zou dit nog steeds de hele rij verwijderen.Rijen met een specifiek woord/waarde verwijderen
U kunt ook een eenvoudige VBA-code gebruiken om door elke cel in het geselecteerde bereik te gaan en alle rijen te verwijderen waarin een cel een specifieke tekst of waarde bevat.
Stel dat u een dataset heeft en ik wil alle cellen verwijderen met de tekst Printer in kolom 2 van de selectie.
Hieronder is de code die dit zal doen:
Sub DeleteRowswithSpecificValue() For i = Selection.Rows.Count To 1 Step -1 If Cells(i, 2).Value = "Printer" Then Cells(i, 2).EntireRow.Delete End If Next i End Sub
De bovenstaande code telt eerst het totale aantal rijen in de selectie. Dit zorgt ervoor dat de lus slechts zo vaak wordt uitgevoerd. Vervolgens gebruikt het de 'For Next-lus' om alle cellen in kolom 2 te doorlopen.
De instructie IF THEN ELSE wordt vervolgens gebruikt om de waarde in elke cel in kolom 2 te controleren. En voor het geval de waarde/tekst overeenkomt met de opgegeven tekst (in dit voorbeeld 'Printer').
In dit voorbeeld heb ik gecontroleerd of de tekst overeenkomt met een specifieke tekenreeks of niet. U kunt dit ook doen met waarden. U kunt bijvoorbeeld alle rijen verwijderen waar de verkoopwaarde kleiner is dan 1000 of meer dan 1000.
Opmerking: een belangrijk ding om op te merken is dat de lus loopt van Selectie Rijen Tellen Tot 1 om ervoor te zorgen dat wanneer een rij wordt verwijderd, dit geen invloed heeft op de rijen erboven.
Hoe deze VBA-code te gebruiken?
Laat me je nu laten zien hoe je alle codes die in deze tutorial worden genoemd, kunt gebruiken om de hele rij te verwijderen.
U moet deze codes kopiëren en plakken in een module in Excel VB Editor. Zodra u deze codes hebt gekopieerd, kunt u de macrocodes uitvoeren.
Hieronder vindt u de stappen om deze VBA-codes in een module te kopiëren en te plakken:
- Houd de ALT-toets ingedrukt en druk op de F11-toets (of Function + Option + F11 in Mac). Dit opent de VB Editor
- In de VB Editor heb je de projectverkenner aan de linkerkant. Als u het niet ziet, gaat u naar de optie Weergeven en klikt u vervolgens op Projectverkenner.
- Klik met de rechtermuisknop op een willekeurig object in de Projectverkenner (voor de werkmap waarin u de code wilt uitvoeren).
- Ga naar Invoegen en klik vervolgens op Module. Hiermee wordt een nieuwe module voor de werkmap ingevoegd
- Kopieer en plak de bovenstaande codes in de module.
En om deze codes uit te voeren, kunt u de cursor overal in de code plaatsen (die u wilt uitvoeren) en op de F5-toets drukken (of op het groene driehoekje in de VBA-werkbalk klikken).
Ik heb ook een gedetailleerde zelfstudie geschreven over verschillende manieren om VBA-macrocodes in Excel uit te voeren.
Als u een van deze codes vrij vaak moet gebruiken, kunt u ook overwegen deze toe te voegen aan de persoonlijke macrowerkmap en vervolgens aan de QAT. Op deze manier is de code met een enkele klik voor u beschikbaar in al uw werkmappen.Dit waren dus enkele VBA-codes die u kunt gebruiken om hele rijen in Excel te verwijderen (in verschillende scenario's). Dezelfde logica kan ook worden toegepast als u kolommen in plaats van rijen wilt verwijderen (met de bijbehorende aanpassing in de codevoorbeelden).
Ik hoop dat je deze tutorial nuttig vond!
