В этом руководстве будет показано, как удалить дубликаты с помощью метода RemoveDuplicates в VBA.
Метод RemoveDuplicates
Когда данные импортируются или вставляются на лист Excel, они часто могут содержать повторяющиеся значения. Возможно, вам потребуется очистить входящие данные и удалить дубликаты.
К счастью, в объекте Range в VBA есть простой метод, который позволяет это сделать.
| 1 | Диапазон («A1: C8»). Столбцы RemoveDuplicates: = 1, Header: = xlYes |
Синтаксис:
RemoveDuplicates ([Столбцы], [Заголовок]
- [Столбцы] - Укажите, в каких столбцах проверяются повторяющиеся значения. Все столбцы совпадают, чтобы считаться дубликатами.
- [Заголовок] - Есть ли у данных заголовок? xlNo (по умолчанию), xlYes, xlYesNoGuess
Технически оба параметра необязательны. Однако, если вы не укажете аргумент Columns, никакие дубликаты не будут удалены.
Значение по умолчанию для заголовка - xlNo. Конечно, лучше указать этот аргумент, но если у вас есть строка заголовка, маловероятно, что строка заголовка будет соответствовать как дубликат.
Замечания по использованию RemoveDuplicates
- Перед использованием метода RemoveDuplicates необходимо указать используемый диапазон.
- Метод RemoveDuplicates удалит все строки с найденными дубликатами, но сохранит исходную строку со всеми значениями.
- Метод RemoveDuplicates работает только со столбцами, а не со строками, но для исправления этой ситуации можно написать код VBA (см. Ниже).
Образцы данных для примеров VBA
Чтобы показать, как работает пример кода, используются следующие образцы данных:
Удалить повторяющиеся строки
Этот код удалит все повторяющиеся строки только на основе значений в столбце A:
| 123 | Sub RemoveDupsEx1 ()Диапазон («A1: C8»). Удалить дубликаты столбцов: = 1, заголовок: = xl ДаКонец подписки |
Обратите внимание, что мы явно определили диапазон «A1: C8». Вместо этого вы можете использовать UsedRange. UsedRange определит последнюю использованную строку и столбец ваших данных и применит RemoveDuplicates ко всему этому диапазону:
| 123 | Sub RemoveDups_UsedRange ()Столбцы ActiveSheet.UsedRange.RemoveDuplicates: = 1, заголовок: = xlYesКонец подписки |
UsedRange невероятно полезен, избавляя вас от необходимости явно определять диапазон.
После запуска этого кода ваш рабочий лист теперь будет выглядеть так:
Обратите внимание, что, поскольку был указан только столбец A (столбец 1), дубликат «Яблоки», ранее находившийся в строке 5, был удален. Однако количество (столбец 2) отличается.
Чтобы удалить дубликаты, сравнивая несколько столбцов, мы можем указать эти столбцы с помощью метода Array.
Удалить дубликаты, сравнивая несколько столбцов
| 123 | Sub RemoveDups_MultColumns ()Столбцы ActiveSheet.UsedRange.RemoveDuplicates: = Массив (1, 2), Заголовок: = xl ДаКонец подписки |
Массив сообщает VBA о необходимости сравнения данных с использованием столбцов 1 и 2 (A и B).
Столбцы в массиве не обязательно должны располагаться в последовательном порядке.
| 123 | Sub SimpleExample ()Столбцы ActiveSheet.UsedRange.RemoveDuplicates: = Массив (3, 1), Заголовок: = xl ДаКонец подписки |
В этом примере столбцы 1 и 3 используются для повторяющегося сравнения.
В этом примере кода для проверки дубликатов используются все три столбца:
| 123 | Sub SimpleExample ()Столбцы ActiveSheet.UsedRange.RemoveDuplicates: = Массив (1, 2, 3), Заголовок: = xl ДаКонец подписки |
Удаление повторяющихся строк из таблицы
Удалить дубликаты можно точно так же применить к таблице Excel. Однако синтаксис немного отличается.
| 1234 | Sub SimpleExample ()Столбцы ActiveSheet.ListObjects ("Table1"). DataBodyRange.RemoveDuplicates: = Array (1, 3), _Заголовок: = xlYesКонец подписки |
Это приведет к удалению дубликатов в таблице на основе столбцов 1 и 3 (A и C). Однако он не приводит в порядок цветовое форматирование таблицы, и вы увидите цветные пустые строки, оставленные в нижней части таблицы.
Удаление дубликатов из массивов
Если вам нужно удалить повторяющиеся значения из массива, конечно, вы можете вывести свой массив в Excel, использовать метод RemoveDuplicates и повторно импортировать массив.
Однако мы также написали процедуру VBA для удаления дубликатов из массива.
Удаление дубликатов из строк данных с помощью VBA
Метод RemoveDuplicates работает только со столбцами данных, но, если подумать «нестандартно», вы можете создать процедуру VBA для работы со строками данных.
Предположим, что ваши данные на листе выглядят так:
У вас есть те же дубликаты, что и раньше, в столбцах B и E, но вы не можете удалить их с помощью метода RemoveDuplicates.
Ответ состоит в том, чтобы использовать VBA для создания дополнительного рабочего листа, копирования данных в него, транспонируя их в столбцы, удаляя дубликаты, а затем копируя их обратно, перемещая обратно в строки.
| 12345678910111213141516171819202122232425262728293031323334353637 | Sub DuplicatesInRows ()'Отключите обновление экрана и предупреждения - мы хотим, чтобы код работал плавно, и пользователь не видел'что здесь происходитApplication.ScreenUpdating = FalseApplication.DisplayAlerts = False'Добавить новый рабочий листТаблицы.Добавить после: = ActiveSheet'Назовите новый рабочий лист' CopySheet 'ActiveSheet.Name = "CopySheet"'Скопируйте данные из исходного листаТаблицы ("DataInRows"). UsedRange.Copy'Активируйте новый созданный листТаблицы ("Копия"). Активировать'Вставить, транспонировать данные так, чтобы они теперь располагались в столбцахActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Operation: = xlNone, SkipBlanks: = _Ложь, Транспонировать: = Истина'Удалите дубликаты столбцов 1 и 3Столбцы ActiveSheet.UsedRange.RemoveDuplicates: = Массив (1, 3), Заголовок _: = xlДа'Очистить данные на исходном листеТаблицы ("DataInRows"). UsedRange.ClearContents'Скопируйте столбцы данных из нового созданного листаТаблицы ("Copysheet"). UsedRange.Copy'Активировать исходный листТаблицы ("DataInRows"). Активировать'Вставить транспонировать недублирующиеся данныеActiveSheet.Range ("A1"). PasteSpecial Paste: = xlPasteAll, Operation: = xlNone, SkipBlanks: = _Ложь, Транспонировать: = Истина'Удалить копию листа - больше не требуетсяТаблицы ("Копия"). Удалить'Активировать исходный листТаблицы ("DataInRows"). Активировать'Включите обновление экрана и предупрежденияApplication.ScreenUpdating = TrueApplication.DisplayAlerts = TrueКонец подписки |
В этом коде предполагается, что исходные данные в строках хранятся на листе под названием «DataInRows».
После запуска кода ваш рабочий лист будет выглядеть так:
Дубликат «Яблоки» в столбце E был удален. Пользователь вернулся в чистое положение, без каких-либо посторонних рабочих листов, и весь процесс прошел гладко, без мерцания экрана или предупреждающих сообщений.
