Deze zelfstudie laat zien hoe u duplicaten verwijdert met behulp van de RemoveDuplicates-methode in VBA.
Methode voor het verwijderen van Duplicaten
Wanneer gegevens worden geïmporteerd of geplakt in een Excel-werkblad, kunnen deze vaak dubbele waarden bevatten. Mogelijk moet u de binnenkomende gegevens opschonen en duplicaten verwijderen.
Gelukkig is er een eenvoudige methode binnen het Range-object van VBA waarmee u dit kunt doen.
| 1 | Bereik ("A1:C8").RemoveDuplicates Columns:=1, Header:=xlJa |
Syntaxis is:
RemoveDuplicates([Kolommen],[Koptekst]
- [Kolommen] - Specificeer welke kolommen worden gecontroleerd op dubbele waarden. Alle kolommen komen veel overeen om als een duplicaat te worden beschouwd.
- [Koptekst] - Hebben gegevens een koptekst? xlNee (standaard), xlJa, xlJaNeeGuess
Technisch gezien zijn beide parameters optioneel. Als u het argument Kolommen echter niet opgeeft, worden er geen duplicaten verwijderd.
De standaardwaarde voor Header is xlNo. Het is natuurlijk beter om dit argument op te geven, maar als je een koprij hebt, is het onwaarschijnlijk dat de koprij als een duplicaat zal overeenkomen.
VerwijderDuplicaten Gebruiksopmerkingen
- Voordat u de methode RemoveDuplicates gebruikt, moet u een bereik opgeven dat moet worden gebruikt.
- De methode RemoveDuplicates verwijdert alle rijen met gevonden duplicaten, maar behoudt de originele rij met alle waarden.
- De methode RemoveDuplicates werkt alleen op kolommen en niet op rijen, maar VBA-code kan worden geschreven om deze situatie te corrigeren (zie later).
Voorbeeldgegevens voor VBA-voorbeelden
Om te laten zien hoe de voorbeeldcode werkt, worden de volgende voorbeeldgegevens gebruikt:
Dubbele rijen verwijderen
Deze code verwijdert alle dubbele rijen alleen op basis van waarden in kolom A:
| 123 | Sub VerwijderDupsEx1()Bereik ("A1:C8").RemoveDuplicates Columns:=1, Header:=xlJaEinde sub |
Merk op dat we het bereik "A1:C8" expliciet hebben gedefinieerd. In plaats daarvan kunt u de UsedRange gebruiken. De UsedRange bepaalt de laatst gebruikte rij en kolom van uw gegevens en past RemoveDuplicates toe op dat hele bereik:
| 123 | Sub RemoveDups_UsedRange()ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlJaEinde sub |
UsedRange is ongelooflijk handig, waardoor u het bereik niet expliciet hoeft te definiëren.
Nadat u deze code hebt uitgevoerd, ziet uw werkblad er nu als volgt uit:
Merk op dat omdat alleen kolom A (kolom 1) is opgegeven, het duplicaat van 'Appels' dat voorheen in rij 5 stond, is verwijderd. De Hoeveelheid (kolom 2) is echter anders.
Om duplicaten te verwijderen door meerdere kolommen te vergelijken, kunnen we die kolommen specificeren met behulp van een Array-methode.
Duplicaten verwijderen Meerdere kolommen vergelijken
| 123 | Sub RemoveDups_MultColumns()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2) , Header:=xlJaEinde sub |
De array vertelt VBA om de gegevens te vergelijken met behulp van zowel kolommen 1 als 2 (A en B).
De kolommen in de array hoeven niet in opeenvolgende volgorde te staan.
| 123 | Sub SimpleExample()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(3, 1) , Header:=xlJaEinde sub |
In dit voorbeeld worden kolommen 1 en 3 gebruikt voor de dubbele vergelijking.
Dit codevoorbeeld gebruikt alle drie de kolommen om te controleren op duplicaten:
| 123 | Sub SimpleExample()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3) , Header:=xlJaEinde sub |
Dubbele rijen uit een tabel verwijderen
De RemoveDuplicates kunnen op precies dezelfde manier ook op een Excel-tabel worden toegepast. De syntaxis is echter iets anders.
| 1234 | Sub SimpleExample()ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), _Koptekst:=xlJaEinde sub |
Hiermee worden de duplicaten in de tabel verwijderd op basis van kolom 1 en 3 (A en C). Het ruimt de kleuropmaak van de tabel echter niet op en u ziet gekleurde lege rijen onder aan de tabel achterblijven.
Duplicaten uit arrays verwijderen
Als u dubbele waarden uit een array moet verwijderen, kunt u uw array natuurlijk in Excel uitvoeren, de methode RemoveDuplicates gebruiken en de array opnieuw importeren.
We hebben echter ook een VBA-procedure geschreven om duplicaten uit een array te verwijderen.
Duplicaten verwijderen uit rijen met gegevens met VBA
De RemoveDuplicates-methode werkt alleen op kolommen met gegevens, maar met wat 'out of the box' denken, kunt u een VBA-procedure maken om met rijen gegevens om te gaan.
Stel dat uw gegevens er op uw werkblad zo uitzien:
U hebt dezelfde duplicaten als voorheen in kolommen B en E, maar u kunt ze niet verwijderen met de methode RemoveDuplicates.
Het antwoord is om VBA te gebruiken om een extra werkblad te maken, de gegevens erin te kopiëren en het in kolommen te transponeren, de duplicaten te verwijderen en het vervolgens terug te kopiëren en het weer in rijen te transponeren.
| 12345678910111213141516171819202122232425262728293031323334353637 | SubduplicatenInRows()'Schakel schermupdates en waarschuwingen uit - we willen dat de code soepel verloopt zonder dat de gebruiker het ziet'wat is er aan de handApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False'Een nieuw werkblad toevoegen'Spreadsheets.Toevoegen na:=ActiveSheet'Noem het nieuwe werkblad 'CopySheet'ActiveSheet.Name = "CopySheet"'Kopieer de gegevens van het originele werkblad'Bladen ("DataInRows").UsedRange.Copy'Activeer het nieuw aangemaakte blad'Spreadsheets ("CopySheet"). Activeren'Plakken transponeer de gegevens zodat deze nu in kolommen staan'ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _False, Transponeren:=True'Verwijder de duplicaten voor kolommen 1 en 3'ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array (1, 3), Header _:=xlJa'Wis de gegevens in het originele werkblad'Spreadsheets ("DataInRows").UsedRange.ClearContents'Kopieer de kolommen met gegevens van het nieuwe werkblad dat is gemaakt'Spreadsheets ("Copysheet").UsedRange.Copy'Activeer het originele blad'Spreadsheets ("DataInRows"). Activeren'Plakken transponeer de niet-dubbele gegevens'ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _False, Transponeren:=True'Verwijder het kopieerblad - niet meer nodigSpreadsheets ("Copysheet").Verwijderen'Activeer het originele blad'Spreadsheets ("DataInRows"). Activeren'Schakel schermupdates en waarschuwingen weer in'Application.ScreenUpdating = TrueApplication.DisplayAlerts = TrueEinde sub |
Deze code gaat ervan uit dat de originele gegevens in rijen worden bewaard op een werkblad met de naam 'DataInRows'
Na het uitvoeren van de code ziet uw werkblad er als volgt uit:
Het duplicaat ‘Appels’ in kolom E is nu verwijderd. De gebruiker bevindt zich weer in een schone positie, zonder overbodige werkbladen die rondhangen, en het hele proces is soepel verlopen zonder flikkerend scherm of waarschuwingsberichten.
