Якщо дані, завантажені з бази даних, перевищують діапазон списку, нові елементи просто не відображаються. Щоб протистояти цьому, діапазон, який лежить в основі списків або комбінованих полів, потрібно розширити або скоротити відповідно до даних. У цій статті розглядається, як це зробити автоматично.
Передбачається, що на пристрої для читання відображається стрічка розробника та він знайомий з редактором VBA. Якщо ні, будь ласка, перегляньте Google “Вкладка розробника Excel” або “Вікно коду Excel”.
Професійним способом відображення комбінованих коробок є розширення чи зменшення їх асортименту за необхідності. Наприклад:
Ключем до динамічного діапазону є відстеження кількості заповнених рядків у відповідному стовпці за допомогою функції = countA. Ця функція підраховує заповнені елементи в послідовності комірок, поки не потрапить до останньої; у випадку першої діаграми на зображенні вище це буде рядок 11.
Для підтримки діапазону автоматично потрібні визначені імена для відстеження кількості заповнених рядків. Наприклад, ми використовуємо eCol (кінцевий стовпець) та eRow (кінцевий рядок), щоб визначити межі нашого діапазону. Чим більше рядків ми заповнюємо, тим більшим стає значення eRow.
Визначені назви вище встановили для списку ширину в один стовпець (eCol = 1) за кількістю заповнених рядків у eCol (eRow = 11)
Нарешті, заголовки з’являються в діапазоні “A2: A” & eRow. Ви зауважите, що функція індексу використовується для встановлення останньої комірки в діапазоні, що називається “Титри”. На цьому етапі діапазон “A2: A” & eRow перекладається на “A2: A11”.
Ми можемо налаштувати динамічний діапазон автоматично, коли книга відкривається, за допомогою підпроцедури Auto_open, яка запускається до того, як книга стане видимою.
Кодекс
Відкрийте книгу та заповніть її полем зі списком та деякими даними. Зразок робочої книги, що використовується у цій вправі, можна знайти тут.
Відкрийте вікно коду VBA та вставте модуль. Скопіюйте код нижче в модуль.
Подія Auto_Open встановлює значення останнього рядка та останнього стовпця для динамічного діапазону “Titles” та зазначає подальші внесені зміни.
Sub auto_open()
Dim eRow As Integer, eCol As Integer, i As Long
On Error Resume Next
'Clear the present define names, to avoid any duplications
activeworkbook.Names("eCol").Delete
activeworkbook.Names("eRow").Delete
activeworkbook.Names("Titles").Delete
Range("A1").Select
'Titles will appear in the first column, A in this case
eCol = 1
'Find the last populated row
eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
'Define the names
activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub
Sub DropDown1_Change()
MsgBox "Directed by " & Cells(2, 5)
End Sub
Примітка: Для спрощення перегляду все розміщено на одній сторінці. Зазвичай значення у стовпцях A та B, а також D та E знаходяться на іншому, можливо, прихованому, аркуші. Також зверніть увагу, що другий стовпець, B, не використовується у визначеннях діапазону в цій конкретній вправі; значення стовпця B отримуються звичайним способом через властивість cell-link поля зі списком (D2 відображає вибір третього елемента в діапазоні, який починається з A2, у поєднанні з функцією Index в «E3» для знаходження директора (=INDEX(B:B,D2+1,1)).
Збережіть книгу, а потім знову відкрийте її. Поле зі списком спочатку заповнюється Auto_open. Додайте елементи до стовпців A та B та спостерігайте за змінами у списку.
Пошкоджені файли Excel
Час від часу файли Excel можуть пошкоджуватися після несподіваного збою Excel. Якщо у вас є резервна копія, ви можете просто відновити дані за допомогою резервної копії. В іншому випадку вам може знадобитися звернутися до професійного експерта або інструменту для відновлення корумпований Excel файли.
Вступ автора:
Фелікс Хукер - фахівець з відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі ремонт rar-файлів та програмні продукти для відновлення sql. Для отримання додаткової інформації відвідайте WWW.datanumen.com