Макрос для удаления повторяющихся строк в Excel
Обновлено: 02.07.2024
Этот макрос удалит строки, которые дважды появляются в списке или на листе. Если две ячейки идентичны, этот макрос удалит каждую строку с идентичной ячейкой, которая находится под первым вхождением. Первый экземпляр останется, но все идентичные ячейки в строках под исходной ячейкой будут удалены. Чтобы запустить макрос, вам нужно выбрать весь столбец, в котором вы хотите искать дубликаты, а затем запустить макрос.
Где установить макрос: Модуль
Удалить повторяющиеся строки в столбце
Общедоступная подпрограмма DeleteDuplicateRows()
' Этот макрос удалит все повторяющиеся строки, которые находятся под
'первым вхождением строки.
'
'Используйте макрос, выбрав столбец для проверки на наличие дубликатов,
'а затем запустите макрос, и все дубликаты будут удалены, оставив
'только первое вхождение.
' /p>
Dim R As Long
Dim N As Long
Dim V As Variant
Dim Rng As Range
V = Rng.Cells(R, 1).Value
If V = vbNullString Then
If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
Rng.Rows(R).EntireRow.Delete
N = N + 1
End If
Else
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(R).EntireRow .Удалить
N = N + 1
Конец, если
Конец, если
Следующий R
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "Повторяющиеся строки удалены: " & CStr(N)
Курс Excel VBA — от новичка до эксперта
200+ видеоуроков 50+ часов обучения 200+ руководств Excel
С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)
Удаление повторяющихся значений во всех версиях Excel
Учебник: Как удалить повторяющиеся значения из набора данных во всех версиях Excel. Это включает пример.
Удалить целые строки на основе предопределенных критериев (текст)
Макрос: этот макрос позволит вам указать определенные критерии, а затем удалить строки на основе.
Удалить скрытые строки в книге
Макрос: этот макрос удалит скрытые строки с каждого листа во всей книге. Однако.
Удаление всех строк, содержащих определенное значение, в Excel
Учебник: быстрый поиск всех строк в Excel, содержащих определенное значение, а затем удаление этих строк. .
Удалить пустые строки в Excel
Макрос: это макрос, который удалит пустые строки в Excel. В этой версии будет удален весь файл .
Магия специальных ячеек: поиск и удаление пустых строк, работа с отфильтрованными данными и многое другое
: как использовать SpecialCells для получения подмножеств диапазонов/ячеек на листе, а затем выполнять другие действия.
- Выделите и скопируйте текст в сером поле выше.
- Откройте файл Microsoft Excel, в котором вы хотите использовать макрос.
- Нажмите «Alt + F11» — откроется редактор Visual Basic — работает для всех версий Excel. Или Чтобы узнать о других способах добраться, нажмите здесь.
Для версий Excel до Excel 2007
Выберите Инструменты > Макросы > Редактор Visual Basic
- Откройте меню в верхней части окна и нажмите "Вставить" > "Модуль".
- В окне редактора Visual Basic должно было открыться другое окно. В этом новом окне вставьте код макроса. Не забудьте вставить код под последней строкой всего, что находится в окне.
- Перейдите к шагу 8.
- Непосредственно под вашим файлом Excel с именем VBAProject (имя вашего файла здесь) щелкните значок папки Microsoft Excel Objects, чтобы открыть раскрывающийся список.
- Затем в нижней части появившегося списка дважды щелкните текст ThisWorkbook.
- Появится новое окно внутри окна редактора Visual Basic. В этом новом окне вставьте код макроса. Не забудьте вставить этот код под последней строкой любого другого кода, который уже находится в окне.
- Перейдите к шагу 8.
- Непосредственно под вашим файлом Excel с именем VBAProject (имя вашего файла здесь) щелкните значок папки Microsoft Excel Objects, чтобы открыть раскрывающийся список.
- В появившемся списке вы увидите все рабочие листы в этом файле Excel. Они будут перечислены как таковые: Лист1 (ЗДЕСЬ НАЗВАНИЕ ЛИСТА), а под ним будет Лист2 (ЗДЕСЬ НАЗВАНИЕ ЛИСТА). Выберите лист, на котором вы хотите запустить макрос, и дважды щелкните этот лист.
- Появится новое окно внутри окна редактора Visual Basic. В этом новом окне вставьте код макроса. Не забудьте вставить этот код под последней строкой любого другого кода, который уже находится в окне.
- Повторите шаги b и c для каждого листа, на котором должен работать макрос.Помещение макроса на один лист не активирует его для других листов книги.
- Перейдите к шагу 8.
- [Столбцы] — укажите, какие столбцы проверяются на повторяющиеся значения. Все столбцы во многом совпадают, чтобы считаться дубликатом.
- [Заголовок] — есть ли у данных заголовок? xlNo (по умолчанию), xlYes, xlYesNoGuess
- Перед использованием метода RemoveDuplicates необходимо указать используемый диапазон.
- Метод RemoveDuplicates удалит все строки с найденными дубликатами, но сохранит исходную строку со всеми значениями.
- Метод RemoveDuplicates работает только со столбцами, а не со строками, но можно написать код VBA, чтобы исправить эту ситуацию (см. далее).
- xlYes: если у данных есть заголовки, вы можете выбрать это.
- xlNo: если у данных нет заголовков, вы можете выбрать это.
- xlGuess: этот параметр позволит Excel угадывать заголовки данных.
Удаление повторяющихся значений во всех версиях Excel
Учебник: Как удалить повторяющиеся значения из набора данных во всех версиях Excel. Это включает пример.
Удалить целые строки на основе предопределенных критериев (текст)
Макрос: этот макрос позволит вам указать определенные критерии, а затем удалить строки на основе.
Удалить скрытые строки в книге
Макрос: этот макрос удалит скрытые строки с каждого листа во всей книге. Однако.
Курс Excel VBA — от новичка до эксперта
200+ видеоуроков
50+ часов видео
200+ руководств по Excel
С помощью этого онлайн-курса станьте мастером VBA и макросов в Excel и узнайте, как автоматизировать все свои задачи в Excel. (Опыт работы с VBA не требуется.)
Когда данные импортируются или вставляются на лист Excel, они часто могут содержать повторяющиеся значения. Возможно, вам потребуется очистить входящие данные и удалить дубликаты.
К счастью, в объекте Range VBA есть простой метод, который позволяет это сделать.
Технически оба параметра являются необязательными. Однако, если вы не укажете аргумент Columns, дубликаты не будут удалены.
Значением по умолчанию для заголовка является xlNo. Конечно, лучше указать этот аргумент, но если у вас есть строка заголовка, маловероятно, что строка заголовка будет совпадать как дубликат.
Примечания по использованию удаления дубликатов
Примеры данных для примеров VBA
Чтобы показать, как работает пример кода, используются следующие примеры данных:
Удалить повторяющиеся строки
Этот код удалит все повторяющиеся строки только на основе значений в столбце A:
Обратите внимание, что мы явно определили диапазон «A1:C8». Вместо этого вы можете использовать UsedRange. UsedRange определит последнюю использованную строку и столбец ваших данных и применит RemoveDuplicates ко всему этому диапазону:
UsedRange невероятно полезен, так как вам не нужно явно определять диапазон.
После запуска этого кода ваш рабочий лист будет выглядеть следующим образом:
Обратите внимание, что, поскольку был указан только столбец A (столбец 1), дубликат «Яблоки», ранее находившийся в строке 5, был удален. Однако количество (столбец 2) отличается.
Чтобы удалить дубликаты, сравнивая несколько столбцов, мы можем указать эти столбцы с помощью метода Array.
Удалить дубликаты при сравнении нескольких столбцов
Массив указывает VBA сравнивать данные, используя оба столбца 1 и 2 (A и B).
Столбцы в массиве не обязательно должны располагаться в последовательном порядке.
В этом примере для сравнения дубликатов используются столбцы 1 и 3.
В этом примере кода для проверки дубликатов используются все три столбца:
Удаление повторяющихся строк из таблицы
Точно таким же образом RemoveDuplicates можно применить и к таблице Excel. Однако синтаксис немного отличается.
Активный лист . Объекты списка ("Таблица1"). DataBodyRange. Удалить дубликаты столбцов: = Массив (1, 3), _
Это удалит дубликаты в таблице на основе столбцов 1 и 3 (A и C). Однако это не убирает цветовое форматирование таблицы, и вы увидите цветные пустые строки, оставленные внизу таблицы.
Программирование VBA стало проще
Прекратите искать код VBA в Интернете. Узнайте больше об AutoMacro — конструкторе кода VBA, который позволяет новичкам создавать процедуры с нуля с минимальными знаниями в области кодирования и множеством функций, позволяющих сэкономить время для всех пользователей!
Удалить дубликаты из массивов
Если вам нужно удалить повторяющиеся значения из массива, конечно, вы можете вывести свой массив в Excel, использовать метод RemoveDuplicates и повторно импортировать массив.
Удаление дубликатов из строк данных с помощью VBA
Метод RemoveDuplicates работает только со столбцами данных, но при некотором нестандартном подходе вы можете создать процедуру VBA для работы со строками данных.
Предположим, что ваши данные на листе выглядят так:
У вас есть те же дубликаты, что и раньше, в столбцах B и E, но вы не можете удалить их с помощью метода RemoveDuplicates.
Ответ заключается в том, чтобы использовать VBA для создания дополнительного рабочего листа, скопировать в него данные, переместив их в столбцы, удалить дубликаты, а затем скопировать их обратно, переместив их обратно в строки.
Используйте метод VBA RemoveDuplicates для удаления повторяющихся строк из столбцов в Excel. Метод RemoveDuplicates — это умный способ упростить диапазон данных.
Я только что вернулся из замечательной поездки в Ирландию, поэтому я собираюсь использовать небольшие данные по Ирландии, чтобы продемонстрировать, как удалять повторяющиеся данные из диапазона.
Если вы предпочитаете работать с массивами, а не с диапазонами, ознакомьтесь с моим руководством, в котором я научу вас, как удалять дубликаты из массива.
Пример — VBA RemoveDuplicates
Удалить строки с повторяющимися значениями в одном столбце
В этом примере удаляется вся строка из диапазона, если значение в третьем столбце повторяется. Продолжайте следовать этому руководству, чтобы понять, как адаптировать код к своему проекту.
Создавайте мощные макросы с помощью нашего бесплатного комплекта разработчика VBA
Такой макрос легко скопировать и вставить, но создать его самостоятельно сложнее. Чтобы помочь вам создавать макросы, подобные этому, мы создали бесплатный комплект разработчика VBA и написали Большую книгу макросов Excel VBA, полную сотен готовых макросов, которые помогут вам освоить файловый ввод-вывод, массивы, строки и многое другое. скопируйте ниже.
Учебное пособие — VBA RemoveDuplicates
Допустим, у вас есть список самых густонаселенных городов Ирландии. Список выглядит примерно так, с названием города, округа и населения:
Вместо того, чтобы перечислять самые густонаселенные города всей страны Ирландии, вы хотите найти самые густонаселенные города в каждом округе.
Обычно вы просматриваете все данные в третьем столбце и удаляете все строки с повторяющимся округом после появления первого экземпляра. Например, вы хотите удалить строки 8 и 18 (ранг 7 и 17), поскольку графство Дублин уже присутствует в строке 2 (ранг 1). Точно так же вы хотите удалить строку 19 (ранг 18), поскольку графство Килдэр уже указано в строке 16 (ранг 15).
Это именно то, что делает приведенный выше пример макроса RemoveDuplicates. Он начинается в верхней части 3-го столбца, столбца округа, и удаляет все последующие дубликаты в этом столбце. У вас остался более короткий список, содержащий самый большой город в каждом округе — все менее населенные города были удалены, поскольку названия округов в менее населенных городах были дубликатами. Это работает, потому что данные уже отсортированы по населению.
Вот список ирландских городов, дубликаты которых были удалены с помощью метода RemoveDuplicates:
Обратите внимание, что список стал короче. Да, некоторые города пересекают несколько округов, поэтому пример не самый лучший, но вы поняли.
Подробнее об удалении дубликатов VBA
Применение удаления дубликатов
Функция RemoveDuplicates должна применяться к определенному диапазону. В моем примере макроса я определил переменную MyRange и установил ее равной данным в столбцах A:D .
Ваш диапазон не обязательно должен быть определен как отдельная переменная. Следующий пример сработал бы так же хорошо:
Предупреждение: как только вы запустите макрос для диапазона с аргументом RemoveDuplicates, повторяющиеся данные исчезнут навсегда. Вы не сможете отменить его и вернуть обратно, не воссоздав данные самостоятельно.
Удалить повторяющиеся аргументы
Метод RemoveDuplicates принимает 2 аргумента:
Первый аргумент, Столбцы, является обязательным, и именно в него вы помещаете столбцы, содержащие дублирующуюся информацию, которую вы хотите очистить. Обратите внимание, я сказал столбцы во множественном числе.
Если у вас есть несколько столбцов, которые вы хотите проверить, вы должны ввести первый аргумент, используя функцию Array, например:
В этом примере я проверяю третий и пятый столбцы. Если оба столбца содержат повторяющуюся информацию, строка данных будет удалена из диапазона.
Второй аргумент, заголовок, является необязательным аргументом со значением по умолчанию xlNo. Вот как вы сообщаете методу RemoveDuplicates, содержат ли ваши данные строку заголовка или нет. Допустимые значения: xlYes, xlNo и xlGuess. Это довольно очевидно, не так ли?
Идеи для приложений
В этом руководстве я привел пример того, как составить список самых густонаселенных городов Ирландии и объединить его в список самых густонаселенных городов в каждом графстве Ирландии.
Я видел, как люди используют RemoveDuplicates VBA для проверки списков, импортированных из нескольких источников. Предположим, у вас есть список имен и адресов электронной почты из двух разных CSV-файлов. Вы хотите объединить их в одну, но хотите, чтобы каждая запись появлялась только один раз.
Для этого скопируйте оба списка в Excel — один поверх другого — и используйте метод RemoveDuplicates для проверки обоих столбцов и удаления повторяющихся записей.
Подпишитесь на нашу серию электронных писем VBA Insiders, чтобы получить дополнительные советы, приемы и тактики VBA, используя форму ниже. После подписки поделитесь тем, что вы автоматизируете, в Twitter и Facebook.
Готовы делать больше с VBA?
Мы собрали гигантский PDF-файл с более чем 300 готовыми макросами и хотим, чтобы вы получили его бесплатно. Введите свой адрес электронной почты ниже, и мы вышлем вам копию вместе с нашим комплектом разработчика VBA, полным советов, приемов и ярлыков VBA.
Прежде чем мы начнем, я хочу сообщить вам, что мы разработали набор шпаргалок по VBA, чтобы вам было проще писать лучшие макросы. Мы включили более 200 советов и 140 примеров макросов, поэтому в них есть все, что вам нужно знать, чтобы стать лучшим программистом VBA.
Повторяющиеся значения часто не требуются в Excel, особенно если вы хотите учитывать уникальные значения. Обычно у нас есть другой набор данных для работы, и мы видим в нем множество повторяющихся значений.
Как удалить повторяющиеся значения в кодировании VBA?
Чтобы сначала удалить повторяющиеся значения, нам нужно указать диапазон, на который мы ссылаемся. Затем мы можем получить доступ к методу «Удалить дубликаты». Таким образом, синтаксис будет следующим.
[Столбец]: Какой столбец выбора нам нужно удалить дубликаты? Нам нужно указать номер столбца выбранного диапазона.
[Заголовок]: выбранный вами диапазон имеет заголовки или нет. Здесь у нас есть три варианта работы.
Таким образом, используя эти параметры, мы можем удалять дубликаты одним нажатием кнопки, не напрягая при этом усилий.
Примеры удаления повторяющихся значений в кодировании VBA
Ниже приведены примеры удаления дубликатов в значениях VBA.
Рассмотрите приведенные ниже данные и для этого примера.
Из приведенных выше данных нам нужно удалить дубликаты столбца «Регион», поэтому выполните следующие шаги, чтобы написать код.
Шаг 1. Запустите подпроцедуру, дав коду макроса имя.
Шаг 2. Укажите диапазон данных с помощью объекта VBA Range.
Код:
Шаг 3. После упоминания метода доступа к диапазону VBA «RemoveDuplicates».
Код:
Шаг 4. Первый аргумент столбца, в котором нужно удалить повторяющиеся значения. В этом примере из первого столбца нам нужно удалить дубликаты.
Код:
Шаг 5. Далее нужно определить, есть ли у данных заголовки. В данном случае у нас есть заголовки, поэтому выберите «xlYes».
Код:
Запустите этот код. Это удалит дубликаты VBA из выбранного региона.
Это явный способ ссылки на диапазон ячеек. Если вы хотите выбрать диапазон самостоятельно, а затем удалить дубликаты, то нам нужно использовать переменную для работы. В приведенном ниже примере я покажу вам, как использовать переменные в VBA.
В приведенном выше примере мы специально указали диапазон ячеек. Теперь мы увидим, как работать с выделением собственных ячеек.
Например, у меня есть несколько наборов данных, как показано на изображении ниже.
Каждый раз я не могу явно указать диапазон ячеек, поэтому мы будем назначать выделение как диапазон.
Шаг 1. Объявите переменную как Range.
Код:
Шаг 2. Диапазон – это объект. Мы установим диапазон в качестве нашего выбора.
Код:
Шаг 3. Теперь вместо диапазона ячеек мы можем использовать переменную «rng».
Код:
Прежде чем мы запустим код, нам нужно сначала выбрать диапазон ячеек. Затем мы можем удалить дубликаты из выбранного диапазона ячеек.
Например, посмотрите на пример изображения данных.
У нас есть повторяющиеся значения в первом и четвертом столбцах. Итак, мы удалим из этих столбцов. Используйте приведенный ниже код для VBA, чтобы удалить дубликаты.
Код:
Вы можете скачать эту программу Excel для удаления дубликатов VBA здесь. Шаблон VBA для удаления дубликатов Excel
Рекомендуемые статьи
Это руководство по удалению дубликатов VBA. Здесь мы узнаем, как удалить повторяющиеся значения в Excel VBA вместе с примерами и загрузим шаблон Excel. Ниже приведены некоторые полезные статьи Excel, связанные с VBA –
Читайте также: