Как найти конец таблицы в excel vba
Обновлено: 08.07.2024
На большом листе, если вы хотите быстро и легко определить последнюю строку или столбец, содержащий значения, вы можете просто перетащить полосу прокрутки, чтобы найти ее. Но будет больно, если на вашем листе будут тысячи строк данных. В этой статье я расскажу о некотором полезном коде VBA, который поможет вам решить эту задачу.
Найдите последнюю строку или столбец с данными с помощью кода VBA
Большинство из нас может подумать о том, чтобы найти последнюю ячейку, нажав Ctrl + Конец клавиши, чтобы найти последнюю строку и столбец, но сочетание клавиш не найдет ячейку с точным значением, если есть некоторые ячейки, форматирование которых находится ниже диапазона данных. Итак, используйте следующий код VBA:
1. Активируйте свой рабочий лист, на котором вы хотите определить последнюю строку или столбец с данными.
2. Удерживайте ALT + F11 , чтобы открыть Окно Microsoft Visual Basic для приложений.
3. Затем нажмите Вставить > Модульи вставьте следующий код в Окно модуля.
Код VBA: найти последнюю строку с данными
4, Затем нажмите F5 нажмите клавишу для запуска этого кода, и появится окно подсказки, в котором будет указан номер строки используемого диапазона. Смотрите скриншот:
5. Продолжайте нажимать OK , на этот раз последний номер строки со значениями был отображен, как показано на следующем снимке экрана:
Примечание: Чтобы найти последний столбец с данными, примените этот код:
Код VBA: найти последний столбец с данными
Найдите и выберите последнюю строку или столбец с данными с помощью Kutools for Excel
Если вы хотите напрямую найти и выбрать последнюю строку или столбец с данными в Excel, Выбрать последнюю строку и горизонтальное распределение Выбрать последний столбец коммунальные услуги Kutools из Excel могу помочь тебе. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Если вы хотите найти и выбрать последнюю строку с данными, нажмите Kutools > Выберите > Выбрать последнюю ячейку > Последняя строка. Смотрите скриншот:
Внимание: Чтобы найти и выбрать последний столбец с данными, нажмите Kutools > Выберите > Выбрать последнюю ячейку > Последний столбец.
Затем будет найдена и немедленно выбрана последняя строка или столбец с данными на текущем листе.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Переменную, которой присваивается номер последней строки, следует объявлять как Long или Variant, например: Dim PosStr As Long . В современных версиях Excel количество строк на рабочем листе превышает максимальное значение типа данных Integer.
Таблица в верхнем левом углу
В первую очередь рассмотрим все доступные варианты поиска номера последней заполненной строки для таблиц, расположенных в верхнем левом углу рабочего листа. Такие таблицы обычно представляют собой простые базы данных в Excel, или, как их еще называют, наборы записей.
Пример таблицы с набором данных в Excel
Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count
Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row
Вариант 4
В первой колонке рабочего листа внутри таблицы не должно быть пропусков, а ниже таблицы в первой колонке не должно быть других заполненных ячеек:
PosStr = WorksheetFunction.CountA(Range("A:A"))
Вариант 5
Ниже таблицы не должно быть никаких записей:
PosStr = Cells.SpecialCells(xlLastCell).Row
Последняя строка любой таблицы
Последнюю заполненную строку для любой таблицы будем искать, отталкиваясь от ее верхней левой ячейки: Cells(a, b) .
Вариант 1
Основная формула для поиска последней строки в любой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row
Вариант 2
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:
PosStr = Cells(a, b).End(xlDown).Row
Если у вас на рабочем листе Excel есть записи вне таблиц, следите за тем, чтобы таблицы были окружены пустыми ячейками или пустыми ячейками и границами листа. Тогда не будет случайно внесенных заметок, примыкающих к таблицам, которые могут отрицательно повлиять на точность вычисления номера последней строки из кода VBA.
Поиск последней строки в столбце является важным аспектом при написании макросов и их динамической обработке. Поскольку мы не хотели бы обновлять диапазоны ячеек время от времени, когда мы работаем со ссылками на ячейки Excel. Будучи программистом / разработчиком, вы всегда предпочитаете писать динамический код, который можно использовать с любыми данными и удовлетворить ваши требования. Более того, было бы здорово, если бы у вас была последняя строка, известная о ваших данных, чтобы вы могли динамически изменять код в соответствии с вашими требованиями.
Я просто укажу один пример, который повторяет важность динамического кода.
Предположим, у меня есть данные, приведенные ниже, с сотрудником и его зарплатой.
И посмотрите на код, приведенный ниже:
Код:
Здесь этот код печатает сумму зарплат для всех сотрудников (ячейка B2: B11 ) в ячейке D2 . Смотрите изображение ниже:
Теперь, что, если я добавлю несколько ячеек в эти данные и снова выполню этот код?
Логически говоря, приведенный выше код не будет суммировать все 14 строк из столбца B. Причина того же - диапазон, который мы обновили в WorksheetFunction (то есть B2: B11). По этой причине динамический код, который может принимать во внимание последнюю заполненную строку, делает нас более важными.
В этой статье я расскажу о некоторых методах, которые могут быть полезны при поиске последней строки для заданного набора данных с использованием кода VBA.
Как найти последнюю использованную строку в столбце, используя VBA?
Ниже приведены различные примеры с различными методами для поиска последней использованной строки столбца в Excel с использованием кода VBA.
Вы можете скачать этот шаблон Excel последней строки VBA здесь - Шаблон Excel последней строки VBA
Что ж, этот метод такой же, как использование стрелки Ctrl + Down в Excel для перехода к последней непустой строке. Аналогичным образом выполните следующие шаги для создания кода в VBA, чтобы добраться до последней непустой строки столбца в Excel.
Шаг 1: Определите переменную, которая может принимать значение для последней непустой строки столбца Excel.
Код:
Здесь переменная Last_Row определена как LONG просто для того, чтобы она могла принимать любое количество аргументов.
Шаг 2: Используйте определенную переменную для хранения значения последней непустой строки.
Код:
Шаг 3: Введите код, начинающийся с CELLS (Rows.Count перед Last_Row = .
Код:
Шаг 4: Упомяните 1 после запятой в вышеприведенном коде. Числовое значение 1 является синонимом первого столбца в таблице Excel.
Код:
Этот код позволяет VBA узнать общее количество (пустых + непустых) строк, присутствующих в первом столбце таблицы Excel. Это означает, что этот код позволяет системе перейти к последней ячейке Excel.
А что, если вы находитесь в последней ячейке Excel и хотите перейти к последней непустой строке? Вы будете использовать Ctrl + Стрелка вверх, верно?
Эту же логику мы будем использовать в следующей строке кода.
Шаг 5: Используйте комбинацию клавиш End и xlUp, чтобы перейти к последней непустой строке в Excel.
Код:
Это приведет вас к последней непустой строке в Excel. Тем не менее, вы хотели номер строки для того же.
Шаг 6: Используйте ROW, чтобы получить номер строки последней непустой строки.
Код:
Шаг 7: Показать значение Last_Row, которое содержит номер последней непустой строки, используя MsgBox.
Код:
Шаг 8: Запустите код, используя кнопку Run или нажав F5, и просмотрите результат.
Выход:
Шаг 9: Теперь давайте удалим одну строку и посмотрим, даст ли код точный результат или нет. Это поможет нам проверить динамичность нашего кода.
Пример № 2 - Использование Range и SpecialCells
Мы также можем использовать свойства Range и SepcialCells VBA, чтобы получить последнюю непустую строку таблицы Excel.
Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:
Шаг 1: Определите переменную снова как Long.
Код:
Шаг 2: Начните сохранять значение в переменной Last_Row с помощью оператора присваивания.
Код:
Шаг 3: Начните вводить диапазон («A: A») .
Код:
Шаг 4: Используйте функцию SpecialCells, чтобы узнать последнюю непустую ячейку.
Код:
Эта функция SpecialCells выбирает последнюю ячейку в вашем Excel, так как в ней указаны круглые скобки ( xlCellTypeLastCell позволяет выбрать последнюю непустую ячейку на вашем листе Excel).
Шаг 5: Теперь используйте ROW, чтобы получить последний ряд из вашего листа Excel.
Код:
Это вернет вам последнюю непустую строку из вашего Excel.
Код:
Шаг 7: Запустите код, нажав F5 или кнопку Run, расположенную в верхней части левого угла.
Выход:
Вы можете видеть, что последний непустой номер ячейки выскакивает через MsgBox со ссылкой на столбец A. Поскольку мы упоминали столбец A в функции Range при определении формулы переменной.
Шаг 8: Если мы удалим строку и сможем запустить эту формулу. Давай посмотрим что происходит.
Вы можете видеть, что система все еще дала счетчик строк как 14. Несмотря на то, что я удалил строку, а фактическое количество строк равно 13, система точно не зафиксировала количество строк. Чтобы система зафиксировала фактическое количество строк, вам нужно сохранить лист и снова запустить код.
Вы можете видеть фактическое количество строк, показанное на этом снимке экрана.
Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:
Шаг 1: Определите переменную как долго.
Код:
Шаг 2: Теперь используйте следующий код, чтобы увидеть последнюю непустую строку.
Код:
Здесь функция FIND ищет первую непустую ячейку. Звездочка (*) - это подстановочный оператор, который помогает выяснить то же самое.
Начиная с ячейки A1, система возвращается к последней ячейке листа и выполняет поиск в обратном направлении (xlPrevious). Он перемещается справа налево (xlByRows) и зацикливается на одном и том же листе по всем строкам на похожих строках, пока не найдет непустую строку (см. .ROW в конце кода).
Шаг 3: Используйте MsgBox, чтобы сохранить значение последней непустой строки и увидеть его как всплывающее окно.
Код:
Шаг 4: Запустите код и просмотрите вывод в виде всплывающего окна, содержащего последний непустой номер строки.
Выход:
То, что нужно запомнить
- End (Example1) может использоваться для определения первой пустой ячейки / строки или последней непустой ячейки / строки в данном столбце с использованием кода VBA.
- Конец работает на одном столбце большую часть времени. Если у вас есть данные в диапазонах, было бы трудно решить, какой столбец следует использовать для поиска последней непустой строки.
- Find (Example3) работает во всем диапазоне от начальной точки и находит последнюю непустую ячейку / строку в данном столбце, используя код VBA. Это также может быть использовано для определения последнего непустого столбца.
Рекомендуемые статьи
Это руководство к VBA Last Row. Здесь мы обсудим, как найти последнюю использованную строку в данном столбце вместе с некоторыми практическими примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow. Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767(это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить
Одинаковые переменные для всех примеров
Dim lLastRow As Long 'а для lLastCol можно применить тип Integer, 'т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
Способ 1:Определение последней заполненной строки через свойство End
определяя таким способом нам надо знать что:
1 - это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.
Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце(или будет заполнена самая последняя ячейка столбца) - то результат будет неверный(ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
1 - это номер строки, последнюю заполненную ячейку в которой мы определяем.
Способ 2:Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.
Определение последней заполненной строки через SpecialCells
Определение последнего столбца через SpecialCells
Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку(строку - Row либо столбец - Column). Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая - следующая за ней. Т.е. к результату необходимо прибавить 1.
Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find
Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул - только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.
Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.
небольшой практический код , который поможет вам понять, как использовать полученную переменную:
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 не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Читайте также: