Групповое выделение специальных ячеек в VBA Excel с помощью метода SpecialCells и констант из коллекции XlCellType, задающих тип выделяемых ячеек.
Выделение специальных ячеек
При разработке кодов на VBA Excel часто требуется работать не со всем диапазоном, а только с ячейками, удовлетворяющими определённым условиям: пустыми, содержащими формулы, ошибки, константы и т.д. Для решения этой задачи используется метод SpecialCells объекта Range.
Ключевой момент: в профессиональном коде VBA Excel рекомендуется метод SpecialCells применять к объекту Range, а не к Selection — как получается при ручной записи макроса с помощью окна «Переход».
Окно «Переход» позволяет вручную выделить на рабочем листе специальные ячейки, но мы будем присваивать их объектной переменной типа Range.
Синтаксис метода SpecialCells
Метод SpecialCells применяется к объекту Range:
|
1 |
RangeObject.SpecialCells(Type, Value) |
Где:
- Type — тип ячеек (перечисление
XlCellType) - Value — (необязательно) уточнение типа данных
Пример:
|
1 |
Range("A1:D100").SpecialCells(xlCellTypeBlanks).Select |
В диапазоне "A1:D100" будут выделены все пустые ячейки.
Таблица констант XlCellType
Список констант из коллекции XlCellType, применяемых в качестве значений параметра Type, и их описания:
| Константа | Описание |
|---|---|
xlCellTypeBlanks |
Пустые ячейки |
xlCellTypeConstants |
Ячейки с константами |
xlCellTypeFormulas |
Ячейки с формулами |
xlCellTypeLastCell |
Последняя ячейка используемого диапазона |
xlCellTypeComments |
Ячейки с комментариями |
xlCellTypeVisible |
Все видимые ячейки |
xlCellTypeAllFormatConditions |
Ячейки с условным форматированием |
xlCellTypeSameFormatConditions |
Одинаковые условия форматирования |
xlCellTypeAllValidation |
Ячейки с проверкой данных |
xlCellTypeSameValidation |
Ячейки с одинаковой проверкой данных |
Использование параметра Value
Параметр Value применяется с типами:
xlCellTypeConstantsxlCellTypeFormulas
Список констант для параметра Value:
| Константа | Значение |
|---|---|
xlNumbers |
Числа |
xlTextValues |
Текст |
xlLogical |
TRUE/FALSE |
xlErrors |
Ошибки |
Пример:
|
1 |
Range("A1:D100").SpecialCells(xlCellTypeFormulas, xlErrors) |
В диапазоне «A1:D100» будут найдены все ячейки с формулами, возвратившими ошибки.
Обработка ошибок
Метод SpecialCells вызывает ошибку, если ячейки, подходящие к заданному типу, отсутствуют.
Корректный шаблон:
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = Range("A1:D100").SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If rng Is Nothing Then MsgBox "Ячейки не найдены" End If |
Практические примеры
Пустые ячейки
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = Range("A1:D100").SpecialCells(xlCellTypeBlanks) On Error GoTo 0 If Not rng Is Nothing Then rng.Interior.Color = vbYellow End If |
Числовые константы
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = Range("A1:D100").SpecialCells(xlCellTypeConstants, xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then rng.Font.Bold = True End If |
Ячейки с формулами
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) On Error GoTo 0 If Not rng Is Nothing Then rng.Interior.Color = vbCyan End If |
Ячейки с комментариями
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments) On Error GoTo 0 If Not rng Is Nothing Then rng.Select End If |
Видимые ячейки (после фильтра)
|
1 2 3 4 5 6 7 8 9 |
Dim rng As Range On Error Resume Next Set rng = Range("A1:D100").SpecialCells(xlCellTypeVisible) On Error GoTo 0 If Not rng Is Nothing Then rng.Copy Destination:=Range("F1") End If |
Selection как частный случай
Selection — это тоже объект типа Range, поэтому код вида:
|
1 |
Selection.SpecialCells(xlCellTypeBlanks) |
формально корректен.
Однако его использование означает, что:
- диапазон определяется пользователем;
- результат зависит от текущего выделения.
Поэтому Selection допустим:
- в макросах для ручного запуска;
- в учебных примерах.
Но в прикладной разработке предпочтительнее задавать диапазон через объект Range, например: ActiveSheet.UsedRange.
Ключевые рекомендации
- Всегда работайте через
Range, а неSelection. - Избегайте метода
.Selectдля выбора (выделения) найденных ячеек, если этого не требуется. - Используйте
On Error Resume Nextдля обхода ошибки, возникающей из-за отсутствия искомых ячеек (Run-time error 1004) и проверяйте результат черезNothing, как в примерах выше. - Ограничивайте область поиска (используйте
ActiveSheet.UsedRangeвместоActiveSheet.Cells). - Избегайте работы с объединёнными ячейками.
- Поиск последней ячейки (
xlCellTypeLastCell) может дать неожиданный результат, если ранее были заполнены ячейки ниже и/или правее заполненного сейчас диапазона. Смотрите пример ниже.
Код:
|
1 2 |
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) MsgBox rng.Address |
дал следующий результат:
Казалось бы, результатом должен быть адрес: $D$14, но нет. Перед запуском кода я ввел произвольный текст в ячейку H18 и затем удалил его. Поэтому мы видим на скриншоте — $H$18, в том числе при повторных запусках этого кода.
Кстати, код:
|
1 2 |
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell) MsgBox rng.Address |
сразу даёт верный результат.
В моей версии Excel после запуска кода с ActiveSheet.UsedRange, запуск кода с ActiveSheet.Cells тоже начинает выдавать правильный результат.
Метод SpecialCells, применяемый к объекту Range, позволяет эффективно выделять ячейки по заданным условиям без перебора и обрабатывать их.
Такой подход делает код предсказуемым, быстрым и независимым от действий пользователя.
Использование объекта Selection остаётся частным случаем, не рекомендуемым для использования, когда этого не требуется, во избежание ошибок.