Как определить последнюю ячейку на листе через VBA?
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long. Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer) нам понадобиться именно Long, во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить
Dim lLastRow As Long 'а для lLastCol можно применить тип Integer, 'т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long |
lLastRow = Cells(Rows.Count,1).End(xlUp).Row |
определяя таким способом нам надо знать что:
1 - это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column |
1 - это номер строки, последнюю заполненную ячейку в которой мы определяем.
Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть
(Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
lLastRow = Cells.SpecialCells(xlLastCell).Row |
lLastCol = Cells.SpecialCells(xlLastCell).Column |
Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку - Row либо столбец - Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 |
lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1 |
- ActiveSheet.UsedRange.Row - этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это - если у вас первые строк 5 не заполнены ничем, то данная строка вернет 6(т.е. номер первой строки с данными). Если же все строки заполнены - то вернет 1.
- ActiveSheet.UsedRange.Rows.Count - определяем кол-во строк, входящих в весь диапазон данных на листе.
Т.е. получается: первая строка данных + кол-во строк с данными - 1. Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 3. Всего строк: 3. 3 + 3 = 6. Вроде все верно, чего тут непонятного? А теперь выделите на листе три ячейки, начиная с 3-ей. Все верно. Ведь у нас в 3-ей строке уже есть данные. Думаю, остальное уже понятно и без моих пояснений. - То же самое и с ActiveSheet.UsedRange.Column, только уже не для строк, а для столбцов.
Обладает всеми недостатками предыдущего метода.. Однако, можно перед определением последней строки/столбца записать строку:
With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.
Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1.
Dim rF As Range Dim lLastRow As Long, lLastCol As Long 'ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find("*", , xlValues, xlWhole, , xlPrevious) If Not rF Is Nothing Then lLastRow = rF.Row 'последняя заполненная строка lLastCol = rF.Column 'последний заполненный столбец MsgBox rF.Address 'показываем сообщение с адресом последней ячейки Else 'если ничего не нашлось - значит лист пустой 'и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 End If |
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.
Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
Sub Get_Last_Cell() Dim lLastRow As Long Dim lLastCol As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row MsgBox "Заполненные ячейки в столбце А: " & Range("A1:A" & lLastRow).Address lLastCol = Cells.SpecialCells(xlLastCell).Column MsgBox "Заполненные ячейки в первой строке: " & Range(Cells(1, 1), Cells(1, lLastCol)).Address MsgBox "Адрес последней ячейки диапазона на листе: " & Cells.SpecialCells(xlLastCell).Address End Sub |
А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:
Sub Copy_To_Last_Cell() Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub |
А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:
Sub Copy_To_Last_Cell() Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub |
Следующим кодом используем инструмент автозаполнение(протягивание) столбца В на основании значения в ячейке B2 и определяя последнюю ячейку для заполнения на основании столбца А
Sub AutoFill_B() Dim lLastRow As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row Range("B2").AutoFill Destination:=Range("B2:B" & lLastRow) End Sub |
Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение "", Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Так же см.:
Как получить последннюю заполненную ячейку формулой?
Как определить первую заполненную ячейку на листе?
Что такое переменная и как правильно её объявить?
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылкиКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Можно чуть подробнее про xlUP, xlCellTypeLastCell ? Заранее спасибо
В вашем примере "А такой код выделит диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:"
Ваш пример выделяет снизу вверх все пустые, но при этом цепляет(захватывает) ближайшие заполненные ячейки диапазона.
Не смог разобраться как выделить диапазон этих самых ячеек ДО последней заполненной? Чтоб были реально только пустые до ближайшей заполненной!
Какие пустые? Код выделяет ровно то, что написано. Если у Вас весь столбец А пуст и заполнена только ячейка A45, то код и выделить ячейки А1:С45.
А Ваша мысль сформулирована как-то непонятно и размыто. Что значить: "только пустые до ближайшей заполненной"? Ближайшей откуда? Сверху? Снизу? Сбоку? Надо найти первую заполненную?(про это тоже есть статья в ссылках ниже этой статьи). Лучше зарегистрируйтесь на форуме и создайте там тему, приложив пример в котором покажите что надо.
Range("A10:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select
Если в диапазоне А1:С10 непустыми являются ячейки с А3:С3, то Ваш макрос выделит диапазон А3:С10. А по идее должен выделить А4:С10(т.к. пустыми являются именно они)
Дмитрий доброго времени!!!А в примере к пункту 4 случаем нет ошибки в строке
Dim lLastRow As Long, lLastRow As Long
вторая переменная не lLastCol As Long случаем?
китин, совершенно верно - ошибка. Спасибо, исправил.
Дмитрий, может быть имеет смысл добавить ещё и способы с использованием SpecialCells при одновременном переопределении UsedRange ?
Sub testLastCell()
Debug.Print "lLastRow = " & ActiveSheet.UsedRange.Cells.SpecialCells(xlLastCell).Row
Debug.Print "iLastCol = " & ActiveSheet.UsedRange.Cells.SpecialCells(xlLastCell).Column
Debug.Print "sLastAddr = " & ActiveSheet.UsedRange.Cells.SpecialCells(xlLastCell).Address
End Sub
Добрый день, Дмитрий! Спасибо за сайт! У Вас интересно и более-менее доступно излагается материал. У меня вопрос по тексту
"А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:".
А если копируемая ячейка находится не на этом листе, а на другом листе и с другого файла
(ее адрес записывается в формате Cells(ActiveCell.Row, 6).Value,
т.е., значение в активной строке и в заданном столбце в другом файле),
находящегося в одной папке с файлом, куда копируются значения, как тогда будет выглядеть код? Пусть это будет, к примеру, дата
Это все хорошо, когда необходимо найти последнюю видимую строку.
Однако, когда надо определить на листе реальную последнюю строку
с данными при фильтре - это не работает, так как и
.End(xlUp).Row, и .SpecialCells(xlCellTypeLastCell).Row
показывают только последнюю видимую строку!
Worksheets(..).UserRange.Rows.Count не годится из-за того,
что учитывает ячейки с ранее удаленными данными
или имевшими форматирование за пределами текущего диапазона данных.
Кроме того, .UserRange сбрасывает метод .UNDO в EXCEL-2003 (в 2010 этого не происходит!),
что ограничивает его использование для EXCEL-2003, если это важно.
Часто рекомендуемые на разных сайтах:
WorksheetFunction(..).Find -ищет также в пределах фильтра,
WorksheetFunction.Match(..) имеет утечку памяти, очень ощутимую при больших таблицах
(проверено на EXCEL-2003 и 2010).
С учетом выше сказанного и с целью ускорения процесса на больших таблицах
(ну, например, для 300000 строк и 40-100 столбцов) реальную строку (столбец)
можно получить, используя .CarrentRegion, который учитывет только непустые ячейки с данными!
Однако надо, чтобы последний диапазон не содержал полностью пустых столбцов или строк.
Вот код для этого:
res=WS.Rows.Count
rup = 0
'для случая, когда таблица начинается не с 1-го столбца, получаем макс строку для 30 столбцов
For k = 1 To 30
rup1 = WS.Cells(res, k).End(xlUp).Row
If rup < rup1 Then rup = rup1
Next k
r1 = WS.Rows(rup).CurrentRegion.Row
r2 = WS.Rows(rup).CurrentRegion.Rows.Count
re = r1 + r2 - 1 'реальная последняя строка
СтрКон = re
'последняя реальная строка данных вне зависимости от фильтра
'одинаково работающая на EXCEL-2003 и 2010
'не сбрасывает метод .UNDO в EXCEL-2003
Прошу прощения, рука дрогнула... UserRange имелось ввиду UsedRange, ну и WS определяется пользователем как Set WS = Worksheets(..)
А этот код определит последнюю заполненную строку при включенном фильтре и срытых сроках в определенном столбце, в данном случае это 3-й столбец "C":
Lastrow = Application.Max(Application.IfError(Application.Match(9E+307, Range("C:C"), 1), 0), Application.IfError(Application.Match("", Range("C:C"), -1), 0))
Дима, погляди способ №4
SearchDirection расположен на месте SearchOrder
Андрей, спасибо. Исправил.
Дим, ну чувствовал, что мне еще чем-то этот код не нравится...
Dim rF As Range
Dim lLastRow As Long, lLastCol As Long
'ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение
' Set rF = ActiveSheet.UsedRange.Find("*", , xlValues, xlWhole, , xlPrevious)
' ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
' при поиске хоть какого-то значения не все ли равно, где искать, в формулах, или значениях,
' в ячейке целиком, или в части ячейки?
' а вот в зависимости от того, что ищем, нужно указать, как ищем
lLastRow = ActiveSheet.UsedRange.Find("*", , , , xlByRows, xlPrevious).Row
lLastCol = ActiveSheet.UsedRange.Find("*", , , , xlByColumns, xlPrevious).Column
Иначе
Set rF = ActiveSheet.UsedRange.Find("*", , , , xlByRows, xlPrevious)
If Not rF Is Nothing Then
lLastRow = rF.Row 'последняя заполненная строка
lLastCol = rF.Column ' столбец последний заполненной ячейки в последней строке
MsgBox rF.Address 'показываем сообщение с адресом последней ячейки
End If
Set rF = ActiveSheet.UsedRange.Find("*", , , , xlByColumns, xlPrevious)
If Not rF Is Nothing Then
lLastCol = rF.Column ' последний заполненный столбец
lLastRow = rF.Row ' строка последней заполненной ячейки в последнем столбце
MsgBox rF.Address 'показываем сообщение с адресом последней ячейки
End If