В этой статье будет продемонстрировано, как создать динамический диапазон в Excel VBA.
Объявление определенного диапазона ячеек в качестве переменной в Excel VBA ограничивает нас работой только с этими конкретными ячейками. Объявляя динамические диапазоны в Excel, мы получаем гораздо больше гибкости в нашем коде и функциональных возможностях, которые он может выполнять.
Ссылка на диапазоны и ячейки
Когда мы ссылаемся на объект Range или Cell в Excel, мы обычно обращаемся к ним путем жесткого кодирования в строке и столбцах, которые нам требуются.
Свойство диапазона
Используя свойство Range в приведенных ниже примерах строк кода, мы можем выполнять действия с этим диапазоном, такие как изменение цвета ячеек или выделение ячеек полужирным шрифтом.
| 12 | Диапазон ("A1: A5"). Font.Color = vbRedДиапазон ("A1: A5"). Полужирный шрифт = True |
Ячейки собственности
Точно так же мы можем использовать свойство Cells для ссылки на диапазон ячеек, напрямую ссылаясь на строку и столбец в свойстве Cells. Строка всегда должна быть числом, но столбец может быть числом или буквой, заключенной в кавычки.
Например, на адрес ячейки A1 можно ссылаться как на:
| 1 | Ячейки (1,1) |
Или
| 1 | Ячейки (1, «А») |
Чтобы использовать свойство Cells для ссылки на диапазон ячеек, нам нужно указать начало диапазона и конец диапазона.
Например, для ссылки на диапазон A1: A6 мы могли бы использовать следующий синтаксис:
| 1 | Диапазон (Ячейки (1,1), Ячейки (1,6) |
Затем мы можем использовать свойство Cells для выполнения действий с диапазоном в соответствии с приведенными ниже примерами кода:
| 12 | Диапазон (Ячейки (2, 2), Ячейки (6, 2)). Font.Color = vbRedДиапазон (Ячейки (2, 2), Ячейки (6, 2)). Жирный шрифт = Истина |
Динамические диапазоны с переменными
Поскольку размер наших данных в Excel изменяется (т.е. мы используем больше строк и столбцов, чем диапазоны, которые мы закодировали), было бы полезно, если бы диапазоны, на которые мы ссылаемся в нашем коде, также изменились. Используя объект Range выше, мы можем создавать переменные для хранения максимальных номеров строк и столбцов в области рабочего листа Excel, которую мы используем, и использовать эти переменные для динамической настройки объекта Range во время выполнения кода.
Например
| 1234 | Dim lRow как целое числоDim lCol как целое числоlRow = Диапазон ("A1048576"). Конец (xlUp) .RowlCol = Range ("XFD1"). End (xlToLeft) .Column |
Последняя строка в столбце
Поскольку на листе 1048576 строк, переменная lRow переместится в нижнюю часть листа, а затем с помощью специальной комбинации клавиши End и клавиши со стрелкой вверх перейдите к последней строке, используемой на листе - это даст нам номер нужной нам строки в нашем диапазоне.
Последний столбец в строке
Аналогичным образом lCol переместится в столбец XFD, который является последним столбцом на листе, а затем с помощью специальной комбинации клавиш, состоящей из клавиши End и клавиши со стрелкой влево, перейдет к последнему столбцу, используемому на листе - это даст нам номер нужного нам столбца в нашем диапазоне.
Следовательно, чтобы получить весь диапазон, который используется на листе, мы можем запустить следующий код:
| 1234567891011 | Sub GetRange ()Dim lRow As IntegerDim lCol как целое числоDim rng As RangelRow = Диапазон ("A1048576"). Конец (xlUp) .Row'используйте lRow, чтобы найти последний столбец в диапазонеlCol = Range ("XFD" & lRow) .End (xlToLeft) .ColumnУстановить rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox, чтобы показать нам диапазонMsgBox "Range is" & rng.AddressКонец подписки |
SpecialCells - LastCell
Мы также можем использовать метод SpecialCells объекта Range, чтобы получить последнюю строку и столбец, используемые в рабочем листе.
| 123456789101112 | Sub UseSpecialCells ()Dim lRow As IntegerDim lCol как целое числоDim rng As RangeDim rngBegin As RangeУстановить rngBegin = Range ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell) .RowlCol = rngBegin.SpecialCells (xlCellTypeLastCell) .ColumnУстановить rng = Range (Cells (1, 1), Cells (lRow, lCol))'msgbox, чтобы показать нам диапазонMsgBox "Range is" & rng.AddressКонец подписки |
UsedRange
Метод используемого диапазона включает все ячейки, в которых есть значения на текущем листе.
| 123456 | Sub UsedRangeExample ()Dim rng As RangeУстановить rng = ActiveSheet.UsedRange'msgbox, чтобы показать нам диапазонMsgBox "Range is" & rng.AddressКонец подписки |
CurrentRegion
Текущая область отличается от UsedRange тем, что она просматривает ячейки, окружающие ячейку, которую мы объявили как начальный диапазон (т.е. переменную rngBegin в приведенном ниже примере), а затем просматривает все ячейки, которые `` прикреплены '' или связаны в объявленную ячейку. Если в строке или столбце появится пустая ячейка, CurrentRegion перестанет искать другие ячейки.
| 12345678 | К югу от CurrentRegion ()Dim rng As RangeDim rngBegin As RangeУстановить rngBegin = Range ("A1")Установите rng = rngBegin.CurrentRegion'msgbox, чтобы показать нам диапазонMsgBox "Range is" & rng.AddressКонец подписки |
Если мы используем этот метод, нам нужно убедиться, что все ячейки в требуемом диапазоне связаны между собой, и среди них нет пустых строк или столбцов.
Именованный диапазон
Мы также можем ссылаться на именованные диапазоны в нашем коде. Именованные диапазоны могут быть динамическими, поскольку при обновлении или вставке данных имя диапазона может измениться, чтобы включить новые данные.
В этом примере шрифт для названия диапазона «Январь» будет изменен на полужирный.
| 12345 | Sub RangeNameExample ()Dim rng as RangeSet rng = Range ("Январь")rng.Font.Bold = = ВерноКонец подписки |
Как вы увидите на рисунке ниже, если строка добавляется в имя диапазона, имя диапазона автоматически обновляется, чтобы включить эту строку.
Если мы затем снова запустим пример кода, диапазон, на который воздействует код, будет C5: C9, тогда как в первом случае это будет C5: C8.
Таблицы
Мы можем ссылаться на таблицы (щелкните для получения дополнительной информации о создании и управлении таблицами в VBA) в нашем коде. Когда данные таблицы в Excel обновляются или изменяются, код, который ссылается на таблицу, будет ссылаться на обновленные данные таблицы. Это особенно полезно при обращении к сводным таблицам, подключенным к внешнему источнику данных.
Используя эту таблицу в нашем коде, мы можем ссылаться на столбцы таблицы по заголовкам в каждом столбце и выполнять действия с столбцом в соответствии с их именем. По мере того, как строки в таблице увеличиваются или уменьшаются в соответствии с данными, диапазон таблицы будет соответствующим образом корректироваться, и наш код будет по-прежнему работать для всего столбца в таблице.
Например:
| 123 | Подраздел DeleteTableColumn ()ActiveWorkbook.Worksheets («Лист1»). ListObjects («Таблица1»). ListColumns («Поставщик»). УдалитьКонец подписки |
