Rijen verwijderen op basis van een celwaarde (of voorwaarde) in Excel (eenvoudige handleiding)

Wanneer u met grote gegevenssets werkt, moet u mogelijk snel rijen verwijderen op basis van de celwaarden erin (of op basis van een voorwaarde).

Denk bijvoorbeeld aan de volgende voorbeelden:

1. U heeft verkoopvertegenwoordigersgegevens en u wilt alle records voor een specifieke regio of product verwijderen.
2. U wilt alle records verwijderen waarvan de verkoopwaarde kleiner is dan 100.
3. U wilt alle rijen met een lege cel verwijderen.

Er zijn meerdere manieren om deze gegevenskat in Excel te villen.

De methode die u kiest om de rijen te verwijderen, hangt af van hoe uw gegevens zijn gestructureerd en wat de celwaarde of voorwaarde is op basis waarvan u deze rijen wilt verwijderen.

In deze zelfstudie laat ik u meerdere manieren zien om rijen in Excel te verwijderen op basis van een celwaarde of een voorwaarde.

Filter rijen op basis van waarde/voorwaarde en verwijder deze vervolgens

Een van de snelste manieren om rijen te verwijderen die een specifieke waarde bevatten of aan een bepaalde voorwaarde voldoen, is door deze te filteren. Zodra u de gefilterde gegevens heeft, kunt u al deze rijen verwijderen (terwijl de resterende rijen intact blijven).

Excel-filter is vrij veelzijdig en u kunt filteren op basis van vele criteria (zoals tekst, getallen, datums en kleuren)

Laten we eens kijken naar twee voorbeelden waarin u de rijen kunt filteren en verwijderen.

Rijen verwijderen die een specifieke tekst bevatten

Stel dat u een gegevensset heeft zoals hieronder weergegeven en dat u alle rijen wilt verwijderen waar de regio Mid-west is (in kolom B).

Hoewel je in deze kleine dataset ervoor kunt kiezen om deze rijen handmatig te verwijderen, zullen je datasets vaak enorm zijn, waarbij het handmatig verwijderen van rijen geen optie is.

In dat geval kunt u alle records filteren waar de regio Mid-West is en vervolgens al deze rijen verwijderen (terwijl de andere rijen intact blijven).

Hieronder vindt u de stappen om rijen te verwijderen op basis van de waarde (alle Mid-West-records):

1. Selecteer een cel in de gegevensset waaruit u de rijen wilt verwijderen
2. Klik op het tabblad Gegevens
3. Klik in de groep 'Sorteren en filteren' op het pictogram Filter. Dit zal filters toepassen op alle koptekstcellen in de dataset
4. Klik op het filterpictogram in de koptekstcel Regio (dit is een klein naar beneden wijzend driehoekje in de rechterbovenhoek van de cel)
5. Deselecteer alle andere opties behalve de optie Mid-West (een snelle manier om dit te doen is door op de optie Alles selecteren te klikken en vervolgens op de optie Mid-West). Hiermee wordt de dataset gefilterd en worden alleen records weergegeven voor de regio Mid-West.
6. Selecteer alle gefilterde records
7. Klik met de rechtermuisknop op een van de geselecteerde cellen en klik op 'Rij verwijderen'
8. Klik in het dialoogvenster dat wordt geopend op OK. Op dit moment ziet u geen records in de dataset.
9. Klik op het tabblad Gegevens en klik op het pictogram Filter. Hierdoor wordt het filter verwijderd en ziet u alle records behalve de verwijderde.

De bovenstaande stappen filteren eerst de gegevens op basis van een celwaarde (of kunnen een andere voorwaarde zijn, zoals na/voor een datum of groter/kleiner dan een getal). Zodra u de records heeft, verwijdert u deze eenvoudig.

Enkele handige sneltoetsen om het proces te versnellen:

1. Control + Shift + L om het filter toe te passen of te verwijderen
2. Bediening + - (houd de control-toets ingedrukt en druk op de min-toets) om de geselecteerde cellen/rijen te verwijderen

In het bovenstaande voorbeeld had ik slechts vier verschillende regio's en ik kon deze handmatig selecteren en deselecteren in de filterlijst (in stap 5 hierboven).

Als u veel categorieën/regio's heeft, kunt u de naam typen in het veld direct boven het vak (dat deze regionamen heeft), en Excel toont u alleen die records die overeenkomen met ingevoerde tekst (zoals hieronder weergegeven). Zodra u de tekst hebt waarop u wilt filteren, drukt u op de Enter-toets.

Houd er rekening mee dat wanneer u een rij verwijdert, alles wat u in andere cellen in deze rijen heeft, verloren gaat. Een manier om dit te omzeilen is door een kopie van de gegevens in een ander werkblad te maken en de rijen in de gekopieerde gegevens te verwijderen. Als u klaar bent, kopieert u het terug in plaats van de originele gegevens.

Of

U kunt de methoden gebruiken die later in deze zelfstudie worden getoond (met behulp van de methode Sorteren of de methode Alles zoeken)

Rijen verwijderen op basis van een numerieke voorwaarde

Net zoals ik de filtermethode heb gebruikt om alle rijen met de tekst Mid-West te verwijderen, kun je ook een getalsvoorwaarde (of een datumvoorwaarde) gebruiken.

Stel dat ik de onderstaande dataset heb en dat ik alle rijen wil verwijderen waar de verkoopwaarde kleiner is dan 200.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer een cel in de gegevens
2. Klik op het tabblad Gegevens
3. Klik in de groep 'Sorteren en filteren' op het pictogram Filter. Dit zal filters toepassen op alle koptekstcellen in de dataset
4. Klik op het filterpictogram in de cel van de koptekst Verkoop (dit is een klein naar beneden wijzend driehoekje in de rechterbovenhoek van de cel)
5. Beweeg de cursor over de optie Nummerfilters. Dit toont u alle nummergerelateerde filteropties in Excel.
6. Klik op de optie 'Minder dan'.
7. Voer in het dialoogvenster 'Aangepast autofilter' dat wordt geopend de waarde '200' in het veld in
8. Klik OK. Hiermee worden alleen die records gefilterd en weergegeven waarvan de verkoopwaarde kleiner is dan 200
9. Selecteer alle gefilterde records
10. Klik met de rechtermuisknop op een van de cellen en klik op Rij verwijderen
11. Klik in het dialoogvenster dat wordt geopend op OK. Op dit moment ziet u geen records in de dataset.
12. Klik op het tabblad Gegevens en klik op het pictogram Filter. Hiermee wordt het filter verwijderd en ziet u alle records behalve de verwijderde.

Er zijn veel cijferfilters die u in Excel kunt gebruiken, zoals kleiner dan/groter dan, gelijk aan/niet gelijk aan, tussen, top 10, boven of onder het gemiddelde, enz.

Opmerking: u kunt ook meerdere filters gebruiken. U kunt bijvoorbeeld alle rijen verwijderen waar de verkoopwaarde groter is dan 200 maar kleiner dan 500. In dit geval moet u twee filtervoorwaarden gebruiken. In het dialoogvenster Aangepast automatisch filteren kunt u twee filtercriteria gebruiken (EN en OF).

Net als de nummerfilters, kunt u de records ook filteren op basis van de datum. Als u bijvoorbeeld alle records van het eerste kwartaal wilt verwijderen, kunt u dat doen door dezelfde stappen hierboven te volgen. Wanneer u met datumfilters werkt, toont Excel automatisch relevante filters (zoals hieronder weergegeven).

Hoewel filteren een geweldige manier is om snel rijen te verwijderen op basis van een waarde of voorwaarde, heeft het één nadeel: het verwijdert de hele rij. In het onderstaande geval zou het bijvoorbeeld alle gegevens verwijderen die zich rechts van de gefilterde gegevensset bevinden.

Wat als ik alleen records uit de dataset wil verwijderen, maar de resterende gegevens intact wil houden.

Dat kan niet met filteren, maar wel met sorteren.

Sorteer de dataset en verwijder vervolgens de rijen

Hoewel sorteren een andere manier is om rijen te verwijderen op basis van waarde, kunt u in de meeste gevallen beter de hierboven beschreven filtermethode gebruiken.

Deze sorteertechniek wordt alleen aanbevolen als u de cellen met de waarden wilt verwijderen en niet de hele rijen.

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt alle records verwijderen waarbij de regio Mid-west is.

Hieronder vindt u de stappen om dit te doen met behulp van sorteren:

1. Selecteer een cel in de gegevens
2. Klik op het tabblad Gegevens
3. Klik in de groep Sorteren en filteren op het pictogram Sorteren.
4. Selecteer in het dialoogvenster Sorteren dat wordt geopend Regio in de kolom Sorteren op.
5. Zorg ervoor dat Celwaarden is geselecteerd in de optie Sorteren op
6. Selecteer in de optie Order A tot Z (of Z tot A, maakt niet echt uit).
7. Klik OK. Dit geeft u de gesorteerde dataset zoals hieronder weergegeven (gesorteerd op kolom B).
8. Selecteer alle records met de regio Mid-West (alle cellen in de rijen, niet alleen de regiokolom)
9. Eenmaal geselecteerd, klik met de rechtermuisknop en klik vervolgens op Verwijderen. Dit opent het dialoogvenster Verwijderen.
10. Zorg ervoor dat de optie 'cellen omhoog verschuiven' is geselecteerd.
11. Klik OK.

Met de bovenstaande stappen worden alle records verwijderd waarin de regio Mid-West was, maar niet de hele rij. Dus als u gegevens rechts of links van uw dataset heeft, blijft deze ongedeerd.

In het bovenstaande voorbeeld heb ik de gegevens gesorteerd op basis van de celwaarde, maar u kunt dezelfde stappen ook gebruiken om te sorteren op basis van getallen, datums, celkleur of letterkleur, enz.

Hier is een gedetailleerde handleiding over het sorteren van gegevens in Excel

Als u de oorspronkelijke volgorde van de gegevensset wilt behouden, maar de records wilt verwijderen op basis van criteria, moet u een manier hebben om de gegevens terug te sorteren naar de oorspronkelijke. Voeg hiervoor een kolom met serienummers toe voordat u de gegevens sorteert. Als u klaar bent met het verwijderen van de rijen/records, kunt u eenvoudig sorteren op basis van deze extra kolom die u hebt toegevoegd.

Zoek en selecteer de cellen op basis van de celwaarde en verwijder vervolgens de rijen

Excel heeft een functie Zoeken en vervangen die geweldig kan zijn als u cellen met een specifieke waarde wilt zoeken en selecteren.

Nadat u deze cellen hebt geselecteerd, kunt u de rijen eenvoudig verwijderen.

Stel dat je de dataset hebt zoals hieronder getoond en je wilt alle rijen verwijderen waar de regio Mid-West is.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de volledige dataset
2. Klik op het tabblad Start
3. Klik in de groep Bewerken op de optie 'Zoeken en selecteren' en klik vervolgens op Zoeken (u kunt ook de sneltoets Control + F gebruiken).
4. Typ in het dialoogvenster Zoeken en vervangen de tekst 'Mid-West' in het veld 'Zoeken naar:'.
5. Klik op Alles zoeken. Dit toont u onmiddellijk alle exemplaren van de tekst Mid-West die Excel kon vinden.
6. Gebruik de sneltoets Control + A om alle cellen te selecteren die Excel heeft gevonden. U kunt ook alle geselecteerde cellen in de gegevensset zien.
7. Klik met de rechtermuisknop op een van de geselecteerde cellen en klik op Verwijderen. Dit opent het dialoogvenster Verwijderen.
8. Selecteer de optie 'Hele rij'
9. Klik OK.

De bovenstaande stappen zouden alle cellen verwijderen waar de regiowaarde Mid-west is.

Opmerking: aangezien Zoeken en vervangen overweg kan met jokertekens, kunt u deze gebruiken bij het zoeken naar gegevens in Excel. Als u bijvoorbeeld alle rijen wilt verwijderen waar de regio Mid-West of South-West is, kunt u '*West' als de tekst die moet worden gevonden in het dialoogvenster Zoeken en vervangen. Dit geeft je alle cellen waar de tekst eindigt met het woord West.

Verwijder alle rijen met een lege cel

Als u alle rijen met lege cellen wilt verwijderen, kunt u dit eenvoudig doen met een ingebouwde functionaliteit in Excel.

Het is de Ga naar speciale cellen optie - waarmee u snel alle lege cellen kunt selecteren. En als je eenmaal alle lege cellen hebt geselecteerd, is het verwijderen ervan supereenvoudig.

Stel dat je de dataset hebt zoals hieronder getoond en ik wil alle rijen verwijderen waar ik de verkoopwaarde niet heb.

Hieronder staan ​​de stappen om dit te doen:

1. Selecteer de volledige dataset (A1:D16 in dit geval).
2. druk de F5 sleutel. Hiermee wordt het dialoogvenster 'Ga naar' geopend (u kunt dit dialoogvenster ook krijgen via Start -> Bewerken -> Zoeken en selecteren -> Ga naar).
3. Klik in het dialoogvenster 'Ga naar' op de knop Speciaal. Hierdoor wordt het dialoogvenster 'Ga naar speciaal' geopend
4. Selecteer in het dialoogvenster Ga naar speciaal 'Leeg'.
5. Klik OK.

Met de bovenstaande stappen worden alle cellen geselecteerd die leeg zijn in de gegevensset.

Zodra u de lege cellen hebt geselecteerd, klikt u met de rechtermuisknop op een van de cellen en klikt u op Verwijderen.

Selecteer in het dialoogvenster Verwijderen de optie 'Volledige rij' en klik op OK. Hiermee worden alle rijen met lege cellen verwijderd.

Als je meer wilt weten over deze techniek, heb ik een gedetailleerde zelfstudie geschreven over het verwijderen van rijen met lege cellen. Het bevat de methode 'Ga naar speciaal' en een VBA-methode om rijen met lege cellen te verwijderen.

Filter en verwijder rijen op basis van celwaarde (met VBA)

De laatste methode die ik je ga laten zien, bevat een klein beetje VBA.

U kunt deze methode gebruiken als u vaak rijen moet verwijderen op basis van een specifieke waarde in een kolom. U kunt de VBA-code één keer toevoegen en deze toevoegen aan uw persoonlijke macro-werkmap. Op deze manier is het beschikbaar voor gebruik in al uw Excel-werkmappen.

Deze code werkt op dezelfde manier als de hierboven beschreven filtermethode (behalve dat dit alle stappen in de backend uitvoert en u enkele klikken bespaart).

Stel dat je de dataset hebt zoals hieronder getoond en je wilt alle rijen verwijderen waar de regio Mid-West is.

Hieronder staat de VBA-code die dit zal doen.

Sub DeleteRowsWithSpecificText() 'Bron:https://trumpexcel.com/delete-rows-based-on-cell-value/ ActiveCell.AutoFilter Veld:=2, Criteria1:="Mid-West" ActiveSheet.AutoFilter.Range.Offset (1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete End Sub

De bovenstaande code gebruikt de VBA Autofilter-methode om eerst de rijen te filteren op basis van de opgegeven criteria (dat is 'Mid-West'), selecteer vervolgens alle gefilterde rijen en verwijder deze.

Merk op dat ik Offset in de bovenstaande code heb gebruikt om ervoor te zorgen dat mijn koprij niet wordt verwijderd.

De bovenstaande code werkt niet als uw gegevens in een Excel-tabel staan. De reden hiervoor is dat Excel een Excel-tabel als een lijstobject beschouwt. Dus als je rijen in een tabel wilt verwijderen, moet je de code een beetje aanpassen (later in deze tutorial behandeld).

Voordat u de rijen verwijdert, ziet u een prompt zoals hieronder weergegeven. Ik vind dit handig omdat ik de gefilterde rij dubbel kan controleren voordat ik deze verwijder.

Onthoud dat wanneer u rijen verwijdert met VBA, u deze wijziging niet ongedaan kunt maken. Gebruik dit dus alleen als u zeker weet dat dit werkt zoals u dat wilt. Het is ook een goed idee om een ​​reservekopie van de gegevens te bewaren voor het geval er iets misgaat.

Als uw gegevens zich in een Excel-tabel bevinden, gebruikt u de onderstaande code om rijen met een specifieke waarde erin te verwijderen:

Sub DeleteRowsinTables() 'Bron:https://trumpexcel.com/delete-rows-based-on-cell-value/ Dim Tbl As ListObject Set Tbl = ActiveSheet.ListObjects(1) ActiveCell.AutoFilter Veld:=2, Criteria1: ="Mid-West" Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete End Sub

Omdat VBA Excel-tabel als een lijstobject (en niet als een bereik) beschouwt, moest ik de code dienovereenkomstig wijzigen.

Waar plaats je de VBA-code?

Deze code moet in de VB Editor backend in een module worden geplaatst.

Hieronder vindt u de stappen die u laten zien hoe u dit kunt doen:

1. Open de werkmap waarin u deze code wilt toevoegen.
2. Gebruik de sneltoets ALT + F11 om het VBA Editor-venster te openen.
3. In dit VBA-editorvenster, aan de linkerkant, bevindt zich een paneel 'Projectverkenner' (met een lijst van alle werkmappen en werkbladobjecten). Klik met de rechtermuisknop op een willekeurig object in de werkmap (waarin u deze code wilt laten werken), beweeg de cursor over 'Invoegen' en klik vervolgens op 'Module'. Hiermee wordt het module-object aan de werkmap toegevoegd en wordt ook het modulecodevenster aan de rechterkant geopend
4. Kopieer en plak de bovenstaande code in het modulevenster (dat aan de rechterkant verschijnt).

Zodra u de code in de VB Editor hebt, kunt u de code uitvoeren met een van de onderstaande methoden (zorg ervoor dat u een willekeurige cel in de dataset hebt geselecteerd waarop u deze code wilt uitvoeren):

1. Selecteer een regel binnen de code en druk op de F5-toets.
2. Klik op de knop Uitvoeren in de werkbalk in de VB Editor
3. Wijs de macro toe aan een knop of een vorm en voer deze uit door erop te klikken in het werkblad zelf
4. Voeg het toe aan de werkbalk Snelle toegang en voer de code uit met een enkele klik.

In dit artikel leest u alles over het uitvoeren van de macrocode in Excel.

Opmerking: aangezien de werkmap een VBA-macrocode bevat, moet u deze opslaan in de macro-enabled indeling (xlsm).