VBA Excel. Отбор уникальных значений с помощью Collection

Отбор уникальных значений из списка в VBA Excel с помощью объекта Collection. Выгрузка уникальных элементов в ListBox и ячейки рабочего листа. Скачать файл с примером кода.

Отбор уникальных значений из списка

При написании макросов для работы с данными в VBA Excel иногда возникает необходимость отбора уникальных значений из списка с повторяющимися элементами. Для этого можно воспользоваться следующим кодом:

На этом отбор уникальных значений завершен. Коллекция заполнена уникальными элементами.

Добавление уникальных элементов в ListBox

Теперь можно добавить уникальные значения в ListBox, если перед этим создать форму UserForm1 и на нее добавить элемент управления ListBox1:

ListBox заполнен уникальными значениями из коллекции. Другие способы заполнения ListBox и ComboBox смотрите здесь.

Запись уникальных значений на рабочий лист

А так можно добавить уникальные элементы в ячейки столбца “В” активного рабочего листа:

При необходимости сортируем полученный список в столбце "В":

А также можно отобразить количество найденных уникальных элементов, если, конечно, на форму UserForm1 добавлен элемент управления Label1:

Если вам необходимо в ListBox или ComboBox загрузить отсортированный список, его элементы можно добавить с листа Excel после сортировки, в данном примере из диапазона Range(Cells(1, 2), Cells(i, 2)).

Обратите внимание, что в представленном коде VBA Excel для отбора уникальных значений из списка, выгрузки их в ListBox и записи на рабочий лист идет сплошная нумерация от Sub ОтборУникальных() и до End Sub.

Для наглядного ознакомления с работой представленного кода вы можете скачать демонстрационный файл.


Смотрите, как удалить повторяющиеся значения из диапазона ячеек в VBA Excel с помощью метода Range.RemoveDuplicates и отобрать уникальные значения из списка с помощью объекта Dictionary.

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

17 комментариев для “VBA Excel. Отбор уникальных значений с помощью Collection”

  1. Холмурод

    Можно ли сделать так :

    у меня не получается, помогите пожалуйста, заранее спасибо!
    С приветом Холмурод.

  2. Евгений

    Привет, Холмурод. В примере указан диапазон из столбца “B”: Range(Cells(1, 2), Cells(i, 2)). Замените его на диапазон из столбца “A”: Range(Cells(1, 1), Cells(i, 1)).

  3. Холмурод

    Привет Евгений.
    в ListBox1 список покажет но Sort Key1:=Range(“A30”) в ячейке A30 ничего нет. Сделал вот так:

  4. Евгений

    Холмурод, а вы хотите сделать, чтобы вставленный на лист список начинался с ячейки A30? Если да, то тогда выгрузку на лист надо начать с 30-й ячейки:

    и для сортировки указать диапазон, начинающийся с 30-й ячейки:

  5. Добрый день,
    В приведенном примере создаётся коллекция уникальных значений. Все работает. Только я не пойму чем, в какой строке определяется уникальность значений этих значений? Где происходит сравнение ? Ведь, наверное, в коллекцию должны записаться все элементы из Range(“A1:A20”) ?

  6. Евгений (автор статьи)

    Привет, Антон.
    Уникальность значений проверяется в строке добавления очередного элемента в коллекцию:

    Первое выражение CStr(myCell.Value) определяет записываемый элемент в коллекцию, второе определяет добавляемый ключ. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибок, перед циклом добавлена строка:

    1. Дмитрий

      Простое и элегантное решение поиска уникальных значений!
      Спасибо!

    2. Камалджан

      Добрый день!
      Странно у меня не срабатывает данный код и выдает ошибку “This key already associated with element of this collection”

    3. Евгений

      Привет, Камалджан!
      Строка

      должна стоять перед строкой

      чтобы пропускать ошибки “This key already associated with element of this collection”, как в исходном коде из этой статьи:

  7. Евгений (автор статьи)

    Антон, это решение я подсмотрел у Джона Уокенбаха в книге «Excel 2010: профессиональное программирование на VBA».

  8. Евгений, хорошо было бы привести пример со словарем в дополнение к коллекции.
    И рассказать как-нибудь про метод Range.RemoveDuplicates

  9. Евгений (автор статьи)

    Привет, Фарин!
    Согласен с вашими пожеланиями, принял их к сведению.

  10. Добрый день!
    Скажите пожалуйста, чувствителен ли данный метод добавления уникальных значений к регистру?
    Я делаю выборку уникальных значений по полю, и значения “Сибирь” и “сибирь” почему то присваиваются одному значению “сибирь”.
    Может быть чувствительность как то отключается/включается?
    Подскажите пожалуйста )

    1. Евгений

      Добрый день, Андрей!
      Ключи объекта Collection нечувствительны к регистру. Если вы хотите отобрать уникальные значения с учетом регистра, используйте код с объектом Dictionary.

Добавить комментарий

Ваш комментарий будет опубликован после прохождения обязательной модерации. Исходящие ссылки не допускаются. Время модерации составит от нескольких минут до нескольких часов в зависимости от времени суток и занятости модератора. При добавлении в комментарий кода VBA Excel, вставьте перед его началом тег <pre> и по окончании кода </pre>.