VBA Excel. Отбор уникальных значений с помощью Collection
При написании макросов для работы с данными в VBA Excel иногда возникает необходимость отбора уникальных значений из списка с повторяющимися элементами. Для этого можно воспользоваться следующим кодом:
На этом отбор уникальных значений завершен. Коллекция заполнена уникальными элементами.
Добавление уникальных элементов в ListBox
Теперь можно добавить уникальные значения в ListBox, если перед этим создать форму UserForm1 и на нее добавить элемент управления ListBox1:
ListBox заполнен уникальными значениями из коллекции. Другие способы заполнения ListBox и ComboBox смотрите здесь.
Запись уникальных значений на рабочий лист
А так можно добавить уникальные элементы в ячейки столбца «В» активного рабочего листа:
При необходимости сортируем полученный список в столбце «В»:
Range ( Cells ( 1 , 2 ) , Cells ( i , 2 ) ) . Sort Key1 : = Range ( «B1» ) , Order1 : = xlAscending , _
А также можно отобразить количество найденных уникальных элементов, если, конечно, на форму UserForm1 добавлен элемент управления Label1:
Если вам необходимо в ListBox или ComboBox загрузить отсортированный список, его элементы можно добавить с листа Excel после сортировки, в данном примере из диапазона Range(Cells(1, 2), Cells(i, 2)).
Обратите внимание, что в представленном коде VBA Excel для отбора уникальных значений из списка, выгрузки их в ListBox и записи на рабочий лист идет сплошная нумерация от Sub ОтборУникальных() и до End Sub.
Для наглядного ознакомления с работой представленного кода вы можете скачать демонстрационный файл.
Смотрите, как удалить повторяющиеся значения из диапазона ячеек в VBA Excel с помощью метода Range.RemoveDuplicates и отобрать уникальные значения из списка с помощью объекта Dictionary.
28 комментариев для “VBA Excel. Отбор уникальных значений с помощью Collection”
Range ( Cells ( 1 , 2 ) , Cells ( i , 2 ) ) . Sort Key1 : = Range ( «А51» ) , Order1 : = xlAscending , _
у меня не получается, помогите пожалуйста, заранее спасибо!
С приветом Холмурод.
Привет, Холмурод. В примере указан диапазон из столбца «B»: Range(Cells(1, 2), Cells(i, 2)). Замените его на диапазон из столбца «A»: Range(Cells(1, 1), Cells(i, 1)).
Привет Евгений.
в ListBox1 список покажет но Sort Key1:=Range(«A30») в ячейке A30 ничего нет. Сделал вот так:
Холмурод, а вы хотите сделать, чтобы вставленный на лист список начинался с ячейки A30? Если да, то тогда выгрузку на лист надо начать с 30-й ячейки:
и для сортировки указать диапазон, начинающийся с 30-й ячейки:
Добрый день,
В приведенном примере создаётся коллекция уникальных значений. Все работает. Только я не пойму чем, в какой строке определяется уникальность значений этих значений? Где происходит сравнение ? Ведь, наверное, в коллекцию должны записаться все элементы из Range(«A1:A20») ?
Привет, Антон.
Уникальность значений проверяется в строке добавления очередного элемента в коллекцию:
Первое выражение CStr(myCell.Value) определяет записываемый элемент в коллекцию, второе определяет добавляемый ключ. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибок, перед циклом добавлена строка:
Простое и элегантное решение поиска уникальных значений!
Спасибо!
Антон, это решение я подсмотрел у Джона Уокенбаха в книге «Excel 2010: профессиональное программирование на VBA».
Евгений, хорошо было бы привести пример со словарем в дополнение к коллекции.
И рассказать как-нибудь про метод Range.RemoveDuplicates
Привет, Фарин!
Согласен с вашими пожеланиями, принял их к сведению.
Добрый день!
Скажите пожалуйста, чувствителен ли данный метод добавления уникальных значений к регистру?
Я делаю выборку уникальных значений по полю, и значения «Сибирь» и «сибирь» почему то присваиваются одному значению «сибирь».
Может быть чувствительность как то отключается/включается?
Подскажите пожалуйста )
Добрый день, Андрей!
Ключи объекта Collection нечувствительны к регистру. Если вы хотите отобрать уникальные значения с учетом регистра, используйте код с объектом Dictionary.
Евгений, спасибо большое за ответ! Принял к использованию )
Здравствуйте, очень интересный метод, но это работает только на простом списке? то есть, можно ли применить этот метод для таблицы из 2-3 столбцов с проверкой на уникальность только по одному столбцу?
Здравствуйте, Вольдемар!
Можно удалить из таблицы строки с неуникальными значениями в одном столбце, проверяя уникальность значений снизу вверх:
мда, это ж надо так догадаться, спасибо, Евгений, я бы сам не додумался
Добрый день, а как сделать так, чтобы уникальные ячейки загружались на лист в определенный столбец?
В выражении i = 10 — 1 число 10 указывает, что заполнение столбца начнется с 10 строки. Если заполнение должно начаться с первой ячейки заданного столбца, это выражение следует исключить из процедуры.
- «Лист1» – наименование ярлыка листа, в ячейки которого будут вставляться уникальные значения;
- «D» – буквенное обозначение столбца, который будет заполняться (можно заменить на числовое значение: 1 вместо «A», 2 вместо «B» и т.д.).
Здравствуйте, помогите пожалуйста нужен код для получения уникального списка по критерию в соседнем столбце
Евгений, добрый день! Только начинаю изучать VBA. Вопросов. Постараюсь в будущем по пустякам не отвлекать).
По существу дела:
1. Первое выражение CStr(c.Value) определяет записываемый элемент в коллекцию, второе определяет добавляемый ключ. Ключ в коллекции не может быть неуникальным, поэтому если он уже есть в коллекции, генерируется ошибка, и цикл переходит к обработке следующей ячейки. Чтобы программа не останавливалась при возникновении ошибок, перед циклом добавлена строка: On Error Resume Next .
2. Элементы коллекции могут содержать разные типы данных, если их не преобразовывать в текст: MyColl.Add c.Value .
3. Я использую коллекции только для отбора уникальных значений, в остальных случаях — массивы. Копирование значений из диапазона ячеек в массив и обратно.
Евгений, от души! Все ясно, я получил исчерпывающий ответ.
P.S. Спасибо за Вашу работу! Этот один из лучших обучающих ресурсов.
Скажите пожалуйста, чувствителен ли данный метод добавления уникальных значений к регистру?
Я делаю выборку уникальных значений по полю, и значения «Сибирь» и «сибирь» почему то присваиваются одному значению «сибирь».
Может быть чувствительность как то отключается/включается?
Подскажите пожалуйста )
VBA Excel. Отбор уникальных значений с помощью Collection
- «Лист1» – наименование ярлыка листа, в ячейки которого будут вставляться уникальные значения;
- «D» – буквенное обозначение столбца, который будет заполняться (можно заменить на числовое значение: 1 вместо «A», 2 вместо «B» и т.д.).
К сожалению, в Excel нет кнопки, которая мгновенно ограничит доступ к той или иной области. Тем не менее всё можно сделать с помощью возможностей программы, не прибегая к другим. Для того чтобы установить подобную защиту от введения неправильных данных или изменения, воспользуйтесь одним из способов:
