Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет
Во всех примерах ниже мы будем запоминать номер последней строки или столбца в одни и те же переменные:
Dim lLastRow As Long 'а для lLastCol можно было бы применить и тип Integer, 'т.к. столбцов в Excel пока меньше 32767, но для однообразности назначим тоже Long Dim lLastCol As Long |
Определение
lLastRow = Cells(Rows.Count,1).End(xlUp).Row 'или lLastRow = Cells(Rows.Count, "A").End(xlUp).Row |
Определение
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column |
Данный метод определения последней строки/столбца самый распространенный. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце(или строке). В большинстве случаев этого более чем достаточно.
Метод основан именно на принципе работы свойства End. На примере поиска последней строки опишу принцип так, как бы мы это делали руками через выделение ячеек на листе:
- выделили самую последнюю ячейку столбца А на листе(для Excel 2007 и выше это
А1048576 , а для Excel 2003 -А65536 )- и выполнили переход вверх комбинацией клавиш
Ctrl +стрелка вверх . Данная комбинация заставляет Excel двигаться вверх(если точнее, то в направлении стрелки, нажатой вместе сCtrl ) до тех пор, пока не встретиться первая ячейка с формулой или значением. А в случае, если сочетание было вызвано из уже заполненных ячеек - то до первой пустой. И как только Excel доходит до этой ячейки - он её выделяет- А через свойство
.Row мы просто получаем номер строки этой выделенной ячейки
- даже если в ячейке нет видимого значения, но есть формула - End посчитает ячейку не пустой. С одной стороны вполне справедливо. Но иногда нам надо определить именно "визуально" заполненные ячейки. Поиск ячеек при подобных условиях будет описан ниже(Способ 4: Определение последней ячейки через метод Find)
- если на листе заполнены все строки в просматриваемом столбце(или будут заполнены несколько последних ячеек столбца или даже только одна последняя) - то результат может быть неверный(ну или не совсем такой, какой ожидали)
- Данный способ игнорирует строки, скрытые фильтром, группировкой или командой Скрыть
(Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
Ну а если надо получить первую пустую ячейку на листе(а не первую заполненную) - придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1. Это хоть и очевидно, но на всякий случай все же лучше об этом напомнить.
Определение
lLastRow = Cells.SpecialCells(xlLastCell).Row |
Определение
lLastCol = Cells.SpecialCells(xlLastCell).Column |
Данный метод не требует указания номера столбца и возвращает последнюю ячейку(Row - строку, Column - столбец).
Если хотите получить номер первой пустой строки или столбца на листе - к результату необходимо прибавить 1.
- Используя данный способ следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Метод
SpecialCells определяет самую "дальнюю" ячейку на листе, используя при этом механизм "запоминания" тех ячеек, в которых мы работали в данном листе. Т.е. если мы занесем в ячейку значение и сразу удалим его - lLastRow, полученная черезAZ90345 SpecialCells , будет равна значению именно той ячейки, из которой мы только что удалили значение(т.е. 90345). Другими словами, этот метод требует обязательного пересмотра рабочего диапазона, а этого можно добиться только сохранив файл, а временами даже только закрыв файл и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то метод SpecialCells посчитает её используемой и будет учитывать как заполненную. Даже больше - может произойти и так, что любое форматирование ячейки(шрифт, заливка, границы) будет считаться её изменением, даже если значение этой ячейки не изменялось, а форматирование после этого было очищено. Помогает только полное удаление строк и столбцов за пределами рабочего диапазона.
Этот недостаток можно попробовать обойти, вызвав перед определением последней ячейки вот такую строку кода:With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.
Выглядеть в единой процедуре это будет так:Sub GetLastCell() Dim lLastRow As Long 'переопределяем рабочий диапазон листа With ActiveSheet.UsedRange: End With 'ищем последнюю заполненную ячейку на листе lLastRow = Cells.SpecialCells(xlLastCell).Row End Sub
- даже если в ячейке нет видимого значения, но есть формула - SpecialCells посчитает ячейку не пустой
- игнорирует установленный фильтр и скрытые строки, определяя последнюю ячейку даже если она скрыта
- Данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование
(Review) -Защитить лист(Protect Sheet) ). - Данный метод не будет работать при использовании внутри UDF. Точнее будет работать не так, как ожидается. Подробнее про некоторые "баги" работы встроенных методов внутри UDF(функций пользователя) я описывал в этой статье: Глюк работы в UDF методов SpecialCells и FindNext
Сам же я этот метод обычно использую для определения последней ячейки в только что созданном файле, в котором только добавляю строки кодом и в котором не может быть описанных выше нюансов.
lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 |
lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1 |
ActiveSheet.UsedRange.Row - этой строкой мы определяем первую ячейку, с которой начинаются данные на листе.Важно понимать для чего это - если у вас первые строк 5 не заполнены ничем(т.е. самые первые данные заносились начиная с 6-ой строки листа), тоActiveSheet.UsedRange.Row вернет именно 6(т.е. номер первой строки с данными). Если же все строки заполнены - то вернет 1.ActiveSheet.UsedRange.Rows.Count - определяем кол-во строк, входящих в весь диапазон данных на листе. При этом неважно, есть ли данные в ячейках или нет - достаточно было поработать в этих ячейках и удалить значения или просто изменить цвет заливки.
В итоге получается:первая строка данных + кол-во строк с данными - 1 . Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 6. Всего строк: 3. 6 + 3 = 9. Вроде все верно. А теперь выделим на листе три ячейки, начиная с 6-ой. Выделение завершилось на 8-ой строке. Потому что в 6-ой строке уже есть данные. Поэтому и надо вычесть 1, чтобы учесть этот момент. Думаю, не надо пояснять, что если надо получить первую пустую ячейку - можно 1 не вычитать :)- То же самое и с
ActiveSheet.UsedRange.Column , только уже не для строк, а для столбцов.
- Обладает некоторыми недостатками предыдущего метода. Определяет самую "дальнюю" ячейку на листе, используя при этом механизм "запоминания" тех ячеек, в которых мы работали в данном листе(включая форматирование ячейки с последующей очисткой). Следовательно попробовать обойти этот момент можно точно так же: перед определением последней строки/столбца записать строку:
With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены. - даже если в ячейке нет видимого значения, но есть формула - UsedRange посчитает ячейку не пустой
- игнорирует установленный фильтр и скрытые строки, определяя последнюю ячейку даже если она скрыта
Однако метод через
Sub GetLastCell_Find() Dim rF As Range Dim lLastRow As Long, lLastCol As Long 'ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False) If Not rF Is Nothing Then lLastRow = rF.Row 'последняя заполненная строка lLastCol = rF.Column 'последний заполненный столбец MsgBox rF.Address 'показываем сообщение с адресом последней ячейки Else 'если ничего не нашлось - значит лист пустой 'и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 MsgBox "A1" 'показываем сообщение с адресом ячейки А1 End If End Sub |
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими при определенных условиях пустую ячейку(
Здесь следует обратить внимание на параметры метода Find. В данном случае мы специально указываем искать по значениям, а не по формулам:
- Метод
Find , вызванный с листа или другим кодом, имеет свойство запоминать все параметры последнего поиска, а если поиск еще не вызывался - то применяются параметры по умолчанию. А по умолчанию поиск идет всегда по формулам. Поэтому я настоятельно рекомендую указывать принудительно все необходимые параметры, как в примере. - Метод
Find не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
Коды ниже могут помочь понять, как использовать приведенные выше строки кода по поиску последней ячейки/строки:
Sub GetLastCell() 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 |
Выделяем диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу
Sub SelectToLastCell() Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select End Sub |
Копируем ячейку
Sub CopyToFstEmptyCell() Dim lLastRow As Long lLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'определили последнюю заполненную ячейку Range("B1").Copy Cells(lLastRow+1, 1) 'скопировали В1 и вставили в следующую после определенной ячейки End Sub |
А код ниже делает тоже самое, но одной строкой - применяется Offset и используется тот факт, что изначально методом End мы получаем именно ячейку, а не номер строки(номер строки мы получаем позже через свойство .Row):
Sub CopyToFstEmptyCell() Range("B1").Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1) End Sub |
Используем инструмент автозаполнение(протягивание) столбца
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.
Так же см.:
Как получить последнюю заполненную ячейку формулой?
Как определить первую заполненную ячейку на листе?
Что такое переменная и как правильно её объявить?
Можно чуть подробнее про 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
Добрый день!
Вот Ваша приписка про "": Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение "", Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
А как можно обойти данное значение? У меня Таблица с динамическим размером. И если нет значений то подставляется символ "" . и соответственное это всегда включается в размер таблицы.
Андрей, так в статье же написано как это обойти - обратите внимание на СПОСОБ 4 :)