Если данные, загруженные из базы данных, превышают диапазон поля со списком, новые элементы просто не отображаются. Чтобы противостоять этому, диапазон, который лежит в основе полей списка или поля со списком, должен расширяться или сжиматься, чтобы соответствовать данным. В этой статье рассматривается, как сделать это автоматически.
Предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».
Профессиональный способ отображения полей со списком заключается в расширении или сужении их диапазонов по мере необходимости. Например:
Ключом к динамическому диапазону является отслеживание количества заполненных строк в соответствующем столбце с помощью функции =countA. Эта функция подсчитывает заполненные элементы в последовательности ячеек, пока не дойдет до последнего; в случае первой диаграммы на изображении выше это будет строка 11.
Для поддержания диапазона автоматически требуются определенные имена для отслеживания количества заполненных строк. Например, мы используем eCol (конец столбца) и eRow (конец строки), чтобы определить границы нашего диапазона. Чем больше строк мы заполняем, тем больше становится значение eRow.
Определенные выше имена установили список шириной в один столбец (eCol = 1) по количеству заполненных строк в eCol (eRow = 11).
Наконец, заголовки появляются в диапазоне «A2:A» и eRow. Вы заметите, что функция индекса используется для установки последней ячейки в диапазоне под названием «Заголовки». Фактически диапазон «A2:A» и eRow на данном этапе преобразуется в «A2:A11».
Мы можем настроить динамический диапазон автоматически при открытии книги, используя подпроцедуру Auto_open, которая запускается до того, как книга станет видимой.
Кодекс
Откройте книгу и заполните ее полем со списком и некоторыми данными. Образец рабочей тетради, использованной в этом упражнении, можно найти здесь.
Откройте окно кода VBA и вставьте модуль. Скопируйте приведенный ниже код в модуль.
Событие Auto_Open устанавливает значения последней строки и последнего столбца для динамического диапазона «Заголовки» и отмечает последующие сделанные изменения.
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 получаются обычным способом с помощью свойства ссылки на ячейку в раскрывающемся списке (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