Ten samouczek pokaże, jak usunąć duplikaty za pomocą metody RemoveDuplicates w VBA.
Metoda usuwania duplikatów
Gdy dane są importowane lub wklejane do arkusza programu Excel, często mogą zawierać zduplikowane wartości. Może być konieczne wyczyszczenie przychodzących danych i usunięcie duplikatów.
Na szczęście istnieje prosta metoda w obiekcie Range VBA, która pozwala to zrobić.
| 1 | Zakres ("A1:C8").UsuńDuplikaty Kolumny:=1, Nagłówek:=xlTak |
Składnia to:
UsuńDuplikaty([Kolumny],[Nagłówek]
- [Kolumny] - Określ, które kolumny są sprawdzane pod kątem zduplikowanych wartości. Wszystkie kolumny w dużym stopniu pasują do duplikatu.
- [Nagłówek] - Czy dane mają nagłówek? xlNie (domyślnie), xlTak, xlTakNieZgadnij
Technicznie oba parametry są opcjonalne. Jeśli jednak nie określisz argumentu Kolumny, żadne duplikaty nie zostaną usunięte.
Domyślna wartość nagłówka to xlNo. Oczywiście lepiej jest określić ten argument, ale jeśli masz wiersz nagłówka, jest mało prawdopodobne, że wiersz nagłówka zostanie dopasowany jako duplikat.
Usuń duplikaty Uwagi dotyczące użytkowania
- Przed użyciem metody RemoveDuplicates należy określić zakres, który ma być używany.
- Metoda RemoveDuplicates usunie wszystkie wiersze ze znalezionymi duplikatami, ale zachowa oryginalny wiersz ze wszystkimi wartościami.
- Metoda RemoveDuplicates działa tylko na kolumnach, a nie na wierszach, ale można napisać kod VBA, aby naprawić tę sytuację (patrz dalej).
Przykładowe dane dla przykładów VBA
Aby pokazać, jak działa przykładowy kod, wykorzystywane są następujące przykładowe dane:
Usuń zduplikowane wiersze
Ten kod usunie wszystkie zduplikowane wiersze na podstawie tylko wartości w kolumnie A:
| 123 | Sub UsuńDupsEx1()Zakres ("A1:C8").UsuńDuplikaty Kolumny:=1, Nagłówek:=xlTakNapis końcowy |
Zauważ, że wyraźnie zdefiniowaliśmy zakres „A1:C8”. Zamiast tego możesz użyć UsedRange. UsedRange określi ostatnio używany wiersz i kolumnę danych i zastosuje RemoveDuplicates do tego całego zakresu:
| 123 | Sub UsuńDups_UsedRange()ActiveSheet.UsedRange.RemoveDuplicates Kolumny:=1, Nagłówek:=xlTakNapis końcowy |
UsedRange jest niezwykle przydatny, eliminując potrzebę jawnego definiowania zakresu.
Po uruchomieniu tego kodu Twój arkusz będzie teraz wyglądał tak:
Zauważ, że ponieważ określono tylko kolumnę A (kolumna 1), duplikat „Jabłka” poprzednio w wierszu 5 został usunięty. Jednak ilość (kolumna 2) jest inna.
Aby usunąć duplikaty, porównując wiele kolumn, możemy określić te kolumny za pomocą metody Array.
Usuń duplikaty porównując wiele kolumn
| 123 | Sub UsuńDups_MultColumns()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2) , Header:=xlYesNapis końcowy |
Array mówi VBA, aby porównał dane przy użyciu obu kolumn 1 i 2 (A i B).
Kolumny w tablicy nie muszą być ułożone w kolejności.
| 123 | Pod prosty przykład()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(3, 1) , Header:=xlYesNapis końcowy |
W tym przykładzie kolumny 1 i 3 są używane do porównania duplikatów.
Ten przykład kodu używa wszystkich trzech kolumn do sprawdzania duplikatów:
| 123 | Pod prosty przykład()ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3) , Header:=xlYesNapis końcowy |
Usuwanie zduplikowanych wierszy z tabeli
RemoveDuplicates można również zastosować do tabeli programu Excel w dokładnie ten sam sposób. Jednak składnia jest nieco inna.
| 1234 | Pod prosty przykład()ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates Columns:=Array(1, 3), _Nagłówek:=xlTakNapis końcowy |
Spowoduje to usunięcie duplikatów w tabeli na podstawie kolumn 1 i 3 (A i C). Jednak nie porządkuje to formatowania kolorów w tabeli, a na dole tabeli pojawią się kolorowe puste wiersze.
Usuń duplikaty z tablic
Jeśli chcesz usunąć zduplikowane wartości z tablicy, oczywiście możesz wyprowadzić tablicę do programu Excel, użyć metody RemoveDuplicates i ponownie zaimportować tablicę.
Jednak napisaliśmy również procedurę VBA, aby usunąć duplikaty z tablicy.
Usuwanie duplikatów z wierszy danych za pomocą VBA
Metoda RemoveDuplicates działa tylko na kolumnach danych, ale przy odrobinie myślenia „po wyjęciu z pudełka” możesz utworzyć procedurę VBA do obsługi wierszy danych.
Załóżmy, że Twoje dane w arkuszu wyglądają tak:
Masz te same duplikaty co wcześniej w kolumnach B i E, ale nie możesz ich usunąć za pomocą metody RemoveDuplicates.
Odpowiedzią jest użycie VBA do utworzenia dodatkowego arkusza roboczego, skopiowania do niego danych transponujących je do kolumn, usunięcia duplikatów, a następnie skopiowania ich z powrotem, transponując je z powrotem do wierszy.
| 12345678910111213141516171819202122232425262728293031323334353637 | Podduplikaty w rzędach()'Wyłącz aktualizacje ekranu i alerty - chcemy, aby kod działał płynnie, tak aby użytkownik nie widział'co się dziejeApplication.ScreenUpdating = FałszApplication.DisplayAlerts = False'Dodaj nowy arkuszArkusze.Dodaj po:=Aktywny arkusz'Wywołaj nowy arkusz 'Kopiuj arkusz'ActiveSheet.Name = "Kopiuj arkusz"'Skopiuj dane z oryginalnego arkuszaSheets("DataInRows").UsedRange.Copy'Aktywuj nowy arkusz, który został utworzonyArkusze("Kopiuj arkusz").Aktywuj„Wklej transponuj dane tak, aby były teraz w kolumnach”ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _Fałsz, transpozycja:=Prawda„Usuń duplikaty dla kolumn 1 i 3”ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 3), Header _:=xlTak„Wyczyść dane w oryginalnym arkuszu”Sheets("DataInRows").UsedRange.ClearContents'Skopiuj kolumny danych z nowo utworzonego arkuszaArkusze("Kopiuj").UsedRange.Copy'Aktywuj oryginalny arkuszArkusze("DataInRows").Aktywuj„Wklej transponuj niezduplikowane dane”ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _Fałsz, transpozycja:=Prawda„Usuń arkusz kopii - już nie jest potrzebny”Arkusze("Kopiuj").Usuń'Aktywuj oryginalny arkuszArkusze("DataInRows").Aktywuj'Włącz ponownie aktualizowanie ekranu i alertyApplication.ScreenUpdating = PrawdaApplication.DisplayAlerts = PrawdaNapis końcowy |
Ten kod zakłada, że oryginalne dane w wierszach są przechowywane w arkuszu o nazwie „DataInRows”
Po uruchomieniu kodu arkusz będzie wyglądał tak:
Duplikat „Jabłka” w kolumnie E został usunięty. Użytkownik jest z powrotem w czystej pozycji, bez wiszących wokół niego zbędnych arkuszy roboczych, a cały proces został wykonany płynnie, bez migotania ekranu lub komunikatów ostrzegawczych.
