У цій статті буде показано, як створити динамічний діапазон у Excel VBA.
Оголошення певного діапазону клітинок як змінної в Excel VBA обмежує роботу лише з цими клітинами. Оголошуючи динамічні діапазони в Excel, ми отримуємо набагато більшу гнучкість щодо нашого коду та функцій, які він може виконувати.
Посилання на діапазони та комірки
Коли ми посилаємось на об’єкт Діапазон або Ячейка в Excel, ми зазвичай звертаємось до них шляхом жорсткого кодування у потрібних нам рядках і стовпцях.
Діапазон Властивість
Використовуючи властивість діапазону, у наведених нижче прикладах рядків коду, ми можемо виконувати дії з цим діапазоном, такі як зміна кольору клітинок або зміцнення клітинок.
| 12 | Діапазон ("A1: A5"). Font.Color = vbRedДіапазон ("A1: A5"). Font.Bold = True |
Власність клітин
Аналогічно, ми можемо використовувати властивість клітинок для посилання на діапазон клітинок, посилаючись безпосередньо на рядок і стовпець у властивості клітинок. Рядок завжди повинен бути числом, але стовпець може бути числом або літерою, укладеною у лапки.
Наприклад, на адресу комірки A1 можна посилатися як:
| 1 | Клітини (1,1) |
Або
| 1 | Клітини (1, "А") |
Щоб використовувати властивість клітинок для посилання на діапазон клітинок, нам потрібно вказати початок діапазону та кінець діапазону.
Наприклад, для довідкового діапазону A1: A6 ми можемо використати цей синтаксис нижче:
| 1 | Діапазон (комірки (1,1), клітинки (1,6) |
Потім ми можемо використовувати властивість Cells для виконання дій з діапазоном відповідно до прикладів рядків коду нижче:
| 12 | Діапазон (комірки (2, 2), клітинки (6, 2)). Шрифт. Колір = vbRedДіапазон (комірки (2, 2), клітинки (6, 2)). Font.Bold = True |
Динамічні діапазони зі змінними
Оскільки розмір наших даних змінюється в Excel (тобто ми використовуємо більше рядків і стовпців, ніж діапазони, які ми закодували), було б корисно, якби змінилися і діапазони, на які ми посилаємось у нашому коді. Використовуючи вищезгаданий об’єкт Range, ми можемо створити змінні для зберігання максимальних номерів рядків і стовпців області робочого аркуша Excel, який ми використовуємо, і використовувати ці змінні для динамічного коригування об’єкта Range під час виконання коду.
Наприклад
| 1234 | Затемнити lRow як ціле числоЗатемнити lCol як ціле числоlRow = Діапазон ("A1048576"). Кінець (xlUp) .RowlCol = Діапазон ("XFD1"). Кінець (xlToLeft). Стовпець |
Останній рядок у колонці
Оскільки на робочому аркуші є 1048576 рядків, змінна lRow перейде до низу аркуша, а потім за допомогою спеціальної комбінації клавіші закінчення плюс клавіші зі стрілкою вгору перейде до останнього рядка, що використовується на аркуші - це дасть нам номер рядка, який нам потрібен у нашому діапазоні.
Остання колонка в рядку
Аналогічно, lCol перейде до стовпця XFD, який є останнім стовпцем на робочому аркуші, а потім за допомогою спеціальної комбінації клавіш клавіші завершення плюс клавіші зі стрілкою вліво перейде до останнього стовпця, використаного на аркуші - це дасть нам номер стовпця, який нам потрібен у нашому діапазоні.
Тому, щоб отримати весь діапазон, який використовується на робочому аркуші, ми можемо запустити такий код:
| 1234567891011 | Sub GetRange ()Затемнити рядок як ціле числоDim lCol як ціле числоЗменшити діапазон як діапазонlRow = Діапазон ("A1048576"). Кінець (xlUp) .Row'використовуйте lRow, щоб допомогти знайти останній стовпець у діапазоніlCol = Діапазон ("XFD" & lRow). Кінець (xlToLeft). СтовпецьВстановити rng = Діапазон (комірки (1, 1), клітинки (lRow, lCol))'msgbox, щоб показати нам діапазонMsgBox "Діапазон" & rng.AdressEnd Sub |
SpecialCells - LastCell
Ми також можемо використовувати метод SpecialCells об'єкта Range, щоб отримати останній рядок і стовпець, що використовуються на робочому аркуші.
| 123456789101112 | Додаткове використанняSpecialCells ()Яскравий ряд як ціле числоDim lCol як ціле числоЗменшити діапазон як діапазонПриглушити rngПочати як діапазонВстановити rngBegin = Діапазон ("A1")lRow = rngBegin.SpecialCells (xlCellTypeLastCell).lCol = rngBegin.SpecialCells (xlCellTypeLastCell).Встановити rng = Діапазон (комірки (1, 1), клітинки (lRow, lCol))'msgbox, щоб показати нам діапазонMsgBox "Діапазон" & rng.AdressEnd Sub |
UsedRange
Метод використаного діапазону включає всі клітинки, які мають значення у поточному аркуші.
| 123456 | Sub UsedRangeExample ()Зменшити діапазон як діапазонВстановіть rng = ActiveSheet.UsedRange'msgbox, щоб показати нам діапазонMsgBox "Діапазон" & rng.AdressEnd Sub |
Поточний регіон
Поточна область відрізняється від UsedRange тим, що вона дивиться на клітинки, що оточують клітинку, яку ми оголосили як початковий діапазон (тобто змінна rngBegin у прикладі нижче), а потім переглядає всі комірки, які "приєднані" або пов'язані до цієї заявленої комірки. Якщо у рядку чи стовпці з’являється порожня клітинка, CurrentRegion припинить пошук будь -яких подальших клітинок.
| 12345678 | Sub CurrentRegion ()Зменшити діапазон як діапазонПриглушити rngПочати як діапазонВстановити rngBegin = Діапазон ("A1")Встановіть rng = rngBegin.CurrentRegion'msgbox, щоб показати нам діапазонMsgBox "Діапазон" & rng.AdressEnd Sub |
Якщо ми використовуємо цей метод, нам потрібно переконатися, що всі клітинки в потрібному вам діапазоні з'єднані без пустих рядків або стовпців.
Іменований діапазон
У нашому коді ми також можемо посилатися на Іменовані діапазони. Іменовані діапазони можуть бути динамічними, оскільки, коли дані оновлюються або вставляються, назва діапазону може змінюватися, включаючи нові дані.
У цьому прикладі шрифт буде змінено напівжирним для назви діапазону “Січень”
| 12345 | ПіддиапазонNameExample ()Змінити rng як діапазонВстановити rng = Діапазон ("Січень")rng.Font.Bold = = ПравдаEnd Sub |
Як ви побачите на малюнку нижче, якщо рядок додано до назви діапазону, то назва діапазону автоматично оновлюється, щоб включити цей рядок.
Якщо б ми знову запустили приклад коду, діапазон, на який впливає код, буде C5: C9, тоді як у першому випадку це буде C5: C8.
Столи
Ми можемо посилатися на таблиці (натисніть для отримання додаткової інформації про створення та маніпулювання таблицями у VBA) у нашому коді. Оскільки дані таблиці в Excel оновлюються або змінюються, код, що посилається на таблицю, буде посилатися на оновлені дані таблиці. Це особливо корисно при зверненні до зведених таблиць, підключених до зовнішнього джерела даних.
Використовуючи цю таблицю в нашому коді, ми можемо посилатися на стовпці таблиці за заголовками кожного стовпця і виконувати дії над стовпцем відповідно до їх назви. Оскільки рядки в таблиці збільшуються або зменшуються відповідно до даних, діапазон таблиці буде відповідно коригуватися, і наш код все одно працюватиме для всього стовпця в таблиці.
Наприклад:
| 123 | Sub DeleteTableColumn ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListColumns ("Supplier"). DeleteEnd Sub |
