Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Как определить последнюю ячейку на листе через VBA?

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

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

Одинаковые переменные для всех примеров
Во всех примерах ниже мы будем запоминать номер последней строки или столбца в одни и те же переменные:

    Dim lLastRow As Long
    'а для lLastCol можно было бы применить и тип Integer,
    'т.к. столбцов в Excel пока меньше 32767, но для однообразности назначим тоже Long
    Dim lLastCol As Long
  • Определение последней ячейки через свойство End
  • Определение последней ячейки через SpecialCells
  • Определение последней ячейки через UsedRange
  • Определение последней ячейки через метод Find
  • Несколько практических кодов


  •  
    Способ 1:
    Определение последней заполненной строки через свойство End

        lLastRow = Cells(Rows.Count,1).End(xlUp).Row
        'или
        lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    1 или "A" - это номер или имя столбца, последнюю заполненную ячейку в котором мы определяем. По сути обе приведенные строки дадут абсолютно одинаковый результат. Просто иногда удобнее указать номер столбца, а иногда его имя. Поэтому использовать можно любой из приведенных вариантов, в зависимости от ситуации.
    Определение последнего столбца через свойство End

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

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

    Данный метод определения последней строки/столбца самый распространенный. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце(или строке). В большинстве случаев этого более чем достаточно.

    Метод основан именно на принципе работы свойства End. На примере поиска последней строки опишу принцип так, как бы мы это делали руками через выделение ячеек на листе:

    • выделили самую последнюю ячейку столбца А на листе(для Excel 2007 и выше это А1048576, а для Excel 2003 - А65536)
    • и выполнили переход вверх комбинацией клавиш Ctrl+стрелка вверх. Данная комбинация заставляет Excel двигаться вверх(если точнее, то в направлении стрелки, нажатой вместе с Ctrl) до тех пор, пока не встретиться первая ячейка с формулой или значением. А в случае, если сочетание было вызвано из уже заполненных ячеек - то до первой пустой. И как только Excel доходит до этой ячейки - он её выделяет
    • А через свойство .Row мы просто получаем номер строки этой выделенной ячейки

    Нюансы:

    • даже если в ячейке нет видимого значения, но есть формула - End посчитает ячейку не пустой. С одной стороны вполне справедливо. Но иногда нам надо определить именно "визуально" заполненные ячейки. Поиск ячеек при подобных условиях будет описан ниже(Способ 4: Определение последней ячейки через метод Find)
    • если на листе заполнены все строки в просматриваемом столбце(или будут заполнены несколько последних ячеек столбца или даже только одна последняя) - то результат может быть неверный(ну или не совсем такой, какой ожидали)
    • Данный способ игнорирует строки, скрытые фильтром, группировкой или командой Скрыть (Hide). Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

    Ну а если надо получить первую пустую ячейку на листе(а не первую заполненную) - придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1. Это хоть и очевидно, но на всякий случай все же лучше об этом напомнить.



     
    Способ 2:
    Определение последней заполненной строки через SpecialCells

        lLastRow = Cells.SpecialCells(xlLastCell).Row

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

        lLastCol = Cells.SpecialCells(xlLastCell).Column

    Данный метод не требует указания номера столбца и возвращает последнюю ячейку(Row - строку, Column - столбец).
    Если хотите получить номер первой пустой строки или столбца на листе - к результату необходимо прибавить 1.
    Нюансы:

    • Используя данный способ следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Метод SpecialCells определяет самую "дальнюю" ячейку на листе, используя при этом механизм "запоминания" тех ячеек, в которых мы работали в данном листе. Т.е. если мы занесем в ячейку AZ90345 значение и сразу удалим его - lLastRow, полученная через 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

    Сам же я этот метод обычно использую для определения последней ячейки в только что созданном файле, в котором только добавляю строки кодом и в котором не может быть описанных выше нюансов.



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

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

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

        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 посчитает ячейку не пустой
    • игнорирует установленный фильтр и скрытые строки, определяя последнюю ячейку даже если она скрыта

    Однако метод через UsedRange.Row работает прекрасно и при установленной на лист защите и внутри UDF, что делает его более предпочтительным, чем метод через SpecialCells при равных условиях.



     
    Способ 4:
    Определение последней строки и столбца, а так же адрес ячейки методом Find

    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

    Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими при определенных условиях пустую ячейку(=ЕСЛИ(A1>0;1;"")), предыдущие варианты вернут строку/столбец ячейки с последней ячейкой, в которой формула. В то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки с формулами и не тратить время на их проверку.
    Здесь следует обратить внимание на параметры метода Find. В данном случае мы специально указываем искать по значениям, а не по формулам:
    Set rF = ActiveSheet.UsedRange.Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False)
    Нюансы:

    • Метод 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

    Выделяем диапазон ячеек в столбцах с А по С, определяя последнюю ячейку по столбцу A этого же листа:

    Sub SelectToLastCell()
        Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    End Sub

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

    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

    Range("B1").Copy - копирует ячейку В1. Если для аргумента Destination указать другую ячейку, то в неё будет вставлена скопированная ячейка. Мы передаем в этот аргумент определенную методом End ячейку
    Cells(Rows.Count, 1).End(xlUp) - возвращает последнюю заполненную ячейку в столбце А (не строку, а именно ячейку)
    Offset(1) - смещает полученную ячейку на строку вниз

    Используем инструмент автозаполнение(протягивание) столбца В, начиная с ячейки 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 ссылки статистика
    Обсуждение: 26 комментариев
    1. Alex_ST:

      Сталкиваюсь у разных авторов с сокращенной по сравнению со Справкой записью НЕКОТОРЫХ констант для SpecialCells, когда в имени константы пропускаются буквы CellType:
      xlCellTypeConstants == xlConstants == 2
      xlCellTypeBlanks == xlBlanks == 4
      xlCellTypeLastCell == xlLastCell == 11
      xlCellTypeVisible == xlVisible == 12
      xlCellTypeFormulas == xlFormulas == -4123
      xlCellTypeComments == xlComments == -4144

      Соответственно, возникают вопросы:
      1. Почему?
      2. Во всех ли версиях Excel допустимо такое сокращение?

    2. Ответ:
      1. Потому что так короче :-)
      2. Да, во всех версиях это будет работать. На самом деле все константы числовые, а их "текстовый" вид задается на уровне приложения(вроде объявления константы в обычном коде).

    3. Anastasia:

      Cells(Rows.Count, 1).End(xlUp).Offset(1)
      Определяет первую пустую ячеку как Active?

    4. Вы текст перед кодом прочитали? - "А вот такой код скопирует ячейку B1 в первую пустую ячейку столбца A этого же листа:"
      Код попробовали? Попробуйте и вопросы отпадут :-)

    5. Anastasia:

      А, например, Range("B1").paste Cells(Rows.Count, 1).End(xlUp).Offset(1) Будет работать?
      Извинете, конечно, за глупые вопросы, просто в первый раз столкнулась с VBA))

    6. Не будет. Рабочие примеры кодов есть в статье. Анастасия, давайте Вы подобные вопросы на форуме задавать будете? Комментарии не предназначены для решения проблем каждого и обучения азам.

    7. Макс:

      Дмитрий такой вопрос. При помощи макроса:

      Sub Test()
          Range(Selection, Selection.End(xlDown)).Select
          Selection.SpecialCells(xlCellTypeLastCell).Select
      End Sub

      выделяю весь диапазон содержащий данные в столбце А. Какой код нужно добавить чтобы после этого выделения, перейти на следующую пустую строку, которая начинается сразу же после окончания диапазона с данными (количество заполненных строк в столбце А постоянно меняется)

    8. Selection.SpecialCells(xlCellTypeLastCell).offset(1).Select
    9. magrifa:

      Дмитрий в вашем примере
      [B1].Copy Cells(Rows.Count, 1).End(xlUp).Offset(1), копируется на этот лист, а можно
      для проверки данных, чтобы копировались на другой лист или нужно создавать
      выпадающий список. Заранее спасибо.

    10. Сергей:

      Спасибо большое автору за короткий код " Range("B1").Copy Cells(Rows.Count, 1).End(xlUp).Offset(1) "! искал 2 дня во всем нете, перепробовал море кодов, а ларчик просто открывался))) - вставил всего 2 строки в макрос и красота!

    Поделитесь своим мнением

    Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


    Для оформления сообщений Вы можете использовать следующие тэги:
    <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

    Тренинги

    Заказать
    Юридическая информация

    Использование материалов сайта

    Политика Конфиденциальности

    ИП Щербаков Дмитрий Валентинович
    ОГРНИП: 318502700083307
    ИНН: 504013350772

    Наши партнеры

    Перейти

    Счетчики

    Рейтинг@Mail.ru Яндекс.Метрика
    © 2024 Excel для всех   Войти