Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.

В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long. Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer) нам понадобиться именно Long, во избежание ошибки.

Одинаковые переменные для всех примеров

    Dim lLastRow As Long
    'а для lLastCol можно применить тип Integer,
    'т.к. столбцов в Excel пока меньше 32767
    Dim lLastCol As Long

    Dim lLastRow As Long
    'а для lLastCol можно применить тип Integer,
    'т.к. столбцов в Excel пока меньше 32767
    Dim lLastCol As Long

Способ 1:

    lLastRow = Cells(Rows.Count,1).End(xlUp).Row

    lLastRow = Cells(Rows.Count,1).End(xlUp).Row

определяя таким способом нам надо знать что:

1 — это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространненый метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.

Правда, следует знать одну вещь: если у Вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) — то результат будет неверный(ну или не совсем такой, какой ожидали увидеть Вы)
Определение последнего столбца

    lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

    lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

1 — это номер строки, последнюю заполненную ячейку в которой мы определяем.

Нет недостатков, присущих второму и третьему способам.  Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой «Скрыть». Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполеннной.

Способ 2:

    lLastRow = Cells.SpecialCells(xlLastCell).Row

    lLastRow = Cells.SpecialCells(xlLastCell).Row

Определение последнего столбца

    lLastCol = Cells.SpecialCells(xlLastCell).Column

    lLastCol = Cells.SpecialCells(xlLastCell).Column

Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку — Row либо столбец — Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку. Если Вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой Вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет засчитана.

Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.

Способ 3:
Определение последней строки

    lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

    lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

Определение последнего столбца

    lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

    lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count - 1

Немного пояснений:
ActiveSheet.UsedRange.Row — этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это — если у Вас первые строк 5 не заполнены ничем, то данная строка вернет 6(т.е. номер первой строки с данными). Если же все строки заполены — то вернет 1.

ActiveSheet.UsedRange.Rows.Count — определяем кол-во строк, входящих в весь диапазон данных на листе.
То же самое и с ActiveSheet.UsedRange.Column, только уже не для строк, а для столбцов.

Обладает всеми недостатками предыдущего метода.

А теперь небольшой практический код, который поможет Вам понять, как использовать полученную переменную:

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

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

А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:

Sub Copy_To_Last_Cell()
    Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub

Sub Copy_To_Last_Cell()
    Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub

Важно знать: необходимо помнить, что если ячейка содержит формулу, пусть и возвращающую значение «», Excel не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.