номер последней заполненной строки vba
VBA Excel. Номер последней заполненной строки
Поиск номера последней заполненной строки с помощью кода VBA Excel для таблиц, расположенных как в верхнем левом углу, так и в любом месте рабочего листа.
Таблица в верхнем левом углу
В первую очередь рассмотрим все доступные варианты поиска номера последней заполненной строки для таблиц, расположенных в верхнем левом углу рабочего листа. Такие таблицы обычно представляют собой простые базы данных в Excel, или, как их еще называют, наборы записей.
Пример таблицы с набором данных в Excel
Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count
Вариант 2
Ниже таблицы не должно быть никаких записей, в том числе ранее удаленных:
PosStr = ActiveSheet.UsedRange.Rows.Count
Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row
Вариант 4
В первой колонке рабочего листа внутри таблицы не должно быть пропусков, а ниже таблицы в первой колонке не должно быть других заполненных ячеек:
PosStr = WorksheetFunction.CountA(Range(«A:A»))
Вариант 5
Ниже таблицы не должно быть никаких записей:
PosStr = Cells.SpecialCells(xlLastCell).Row
Последняя строка любой таблицы
Вариант 1
Основная формула для поиска последней строки в любой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row
Вариант 2
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:
PosStr = Cells(a, b).End(xlDown).Row
Если у вас на рабочем листе Excel есть записи вне таблиц, следите за тем, чтобы таблицы были окружены пустыми ячейками или пустыми ячейками и границами листа. Тогда не будет случайно внесенных заметок, примыкающих к таблицам, которые могут отрицательно повлиять на точность вычисления номера последней строки из кода VBA.
Номер последней заполненной строки vba
Вариант II.
Для поиска последней заполненной ячейки можно воспользоваться свойством UsedRange об’екта Worksheet
Вариант III.
Для определения количества заполненных ячеек в смежном диапазоне можно воспользоваться свойством CurrentRegion об’екта Range
|
iClm = Rows(1).CurrentRegion.Columns.Count
iAddress = Range(«A1»).CurrentRegion.Address
Особенностью свойства CurrentRegion является то, что он возвращает весь диапазон, но только состоящий из смежных ячеек.
Комментарий :
важно При определении последней заполненной строки, этот вариант будет корректно работать только при условии, что данные в строке и столбце начинаются с самой первой ячейки и не содержат пустых строк и столбцов.
Предупреждение :
Если рабочий лист защищён, то использование этого варианта приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.
Вариант IV.
Для определения номера строки и столбца последней заполненной ячейки можно использовать функцию ПОЛУЧИТЬ.ДОКУМЕНТ (макроязык Excel4.0)
|
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12)»)
|
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»Лист1″»)»)
|
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»[ОткрытаяКнига.xls]Лист1″»)»)
Вариант V.
Для определения номера последней заполненной ячейки в конкретной строке или столбце, а также для определения последней заполненной ячейки можно использовать метод Find
|
iRow = Columns(«C»).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Rows(10).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
|
iRow = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
Вышеупомянутый синтаксис может вызвать ошибку, если указанный диапазон не содержит данных. Для того, чтобы этого избежать, во всех примерах использован «режим отложенной ошибки» On Error Resume Next
Однако, можно использовать и другой синтаксис, например :
If Not iLastCell Is Nothing Then
iRow = iLastCell.Row
iClm = iLastCell.Column
End If
If TypeName(iLastCell) <> «Nothing» Then
iRow = iLastCell.Row
iClm = iLastCell.Column
End If
важно Этот вариант будет корректно работать только при условии, что ячейки не содержат формул, которые возвращают пустую строку «» или апостроф ‘
|
iRow = Columns(«C»).End(xlDown).Row
iClm = Rows(10).End(xlToRight).Column
важно Этот вариант будет корректно работать только при условии, что данные в строке, или столбце начинаются с самой первой ячейки и не содержат пустых ячеек.
Вариант VII.
Тот же самый способ, что и предыдущий, но с небольшими изменениями.
|
iRow = Cells(65536, «C»).End(xlUp).Row
iClm = Cells(10, 256).End(xlToLeft).Column
iRow = Cells(Rows.Count, 3).End(xlUp).Row
iRow = Cells(Rows.Count, «C»).End(xlUp).Row
iClm = Cells(10, Columns.Count).End(xlToLeft).Column
iRow = Columns(«C»).Rows(65536).End(xlUp).Row
важно Этот вариант будет работать при любых условиях, так как маловероятно, что последней заполненной ячейкой окажется именно последняя ячейка в столбце, однако и эту вероятность можно учесть :
|
iRow = Application.CountA(Columns(«C»))
iRow = WorksheetFunction.CountA(Columns(3))
iRow = Excel.Application.CountA(Columns(«C»))
iRow = Excel.WorksheetFunction.CountA(Columns(3))
iRow = Application.WorksheetFunction.CountA(Columns(«C»))
важно Так как функция СЧЁТЗ считает количество непустых ячеек, то этот вариант будет корректно работать только при условии, что данные в столбце начинаются с самой первой ячейки и не содержат пустых ячеек.
Важно : Если быть абсолютно точным, то в первых пяти случаях мы определяем 
последнюю ячейку, которая вовсе не обязательно должна быть заполнена, так как эта ячейка расположена на пересечении последней строки и последнего столбца.
Причём, в случае применения первого, второго или четвёртого варианта, необходимо помнить, что последними могут считаться ячейки, параметры форматирования которых, отличаются от изначально установленных.
Тогда цикл по строкам будет выглядеть примерно так :
Как определить последнюю ячейку на листе через VBA?
Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.
Одинаковые переменные для всех примеров
Dim lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long
lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Определение последнего столбца через SpecialCells
Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.
Способ 3:
Определение последней строки через UsedRange
Определение последнего столбца через UsedRange
Способ 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 не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Статья помогла? Поделись ссылкой с друзьями!
VBA Last Row
Excel VBA Последняя строка
Поиск последней строки в столбце является важным аспектом при написании макросов и их динамической обработке. Поскольку мы не хотели бы обновлять диапазоны ячеек время от времени, когда мы работаем со ссылками на ячейки Excel. Будучи программистом / разработчиком, вы всегда предпочитаете писать динамический код, который можно использовать с любыми данными и удовлетворить ваши требования. Более того, было бы здорово, если бы у вас была последняя строка, известная о ваших данных, чтобы вы могли динамически изменять код в соответствии с вашими требованиями.
Я просто укажу один пример, который повторяет важность динамического кода.
Предположим, у меня есть данные, приведенные ниже, с сотрудником и его зарплатой.
И посмотрите на код, приведенный ниже:
Теперь, что, если я добавлю несколько ячеек в эти данные и снова выполню этот код?
В этой статье я расскажу о некоторых методах, которые могут быть полезны при поиске последней строки для заданного набора данных с использованием кода VBA.
Как найти последнюю использованную строку в столбце, используя VBA?
Ниже приведены различные примеры с различными методами для поиска последней использованной строки столбца в Excel с использованием кода VBA.
Что ж, этот метод такой же, как использование стрелки Ctrl + Down в Excel для перехода к последней непустой строке. Аналогичным образом выполните следующие шаги для создания кода в VBA, чтобы добраться до последней непустой строки столбца в Excel.
Шаг 1: Определите переменную, которая может принимать значение для последней непустой строки столбца Excel.
Здесь переменная Last_Row определена как LONG просто для того, чтобы она могла принимать любое количество аргументов.
Шаг 2: Используйте определенную переменную для хранения значения последней непустой строки.
Шаг 4: Упомяните 1 после запятой в вышеприведенном коде. Числовое значение 1 является синонимом первого столбца в таблице Excel.
Этот код позволяет VBA узнать общее количество (пустых + непустых) строк, присутствующих в первом столбце таблицы Excel. Это означает, что этот код позволяет системе перейти к последней ячейке Excel.
А что, если вы находитесь в последней ячейке Excel и хотите перейти к последней непустой строке? Вы будете использовать Ctrl + Стрелка вверх, верно?
Эту же логику мы будем использовать в следующей строке кода.
Шаг 5: Используйте комбинацию клавиш End и xlUp, чтобы перейти к последней непустой строке в Excel.
Это приведет вас к последней непустой строке в Excel. Тем не менее, вы хотели номер строки для того же.
Шаг 6: Используйте ROW, чтобы получить номер строки последней непустой строки.
Шаг 7: Показать значение Last_Row, которое содержит номер последней непустой строки, используя MsgBox.
Шаг 8: Запустите код, используя кнопку Run или нажав F5, и просмотрите результат.
Выход:
Шаг 9: Теперь давайте удалим одну строку и посмотрим, даст ли код точный результат или нет. Это поможет нам проверить динамичность нашего кода.
Мы также можем использовать свойства Range и SepcialCells VBA, чтобы получить последнюю непустую строку таблицы Excel.
Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:
Шаг 1: Определите переменную снова как Long.
Шаг 2: Начните сохранять значение в переменной Last_Row с помощью оператора присваивания.
Шаг 4: Используйте функцию SpecialCells, чтобы узнать последнюю непустую ячейку.
Эта функция SpecialCells выбирает последнюю ячейку в вашем Excel, так как в ней указаны круглые скобки ( xlCellTypeLastCell позволяет выбрать последнюю непустую ячейку на вашем листе Excel).
Шаг 5: Теперь используйте ROW, чтобы получить последний ряд из вашего листа Excel.
Это вернет вам последнюю непустую строку из вашего Excel.
Шаг 6: Теперь присвойте MsgBox это значение Last_Row, чтобы мы могли видеть последний непустой номер строки в окне сообщения.
Шаг 7: Запустите код, нажав F5 или кнопку Run, расположенную в верхней части левого угла.
Выход:
Вы можете видеть, что последний непустой номер ячейки выскакивает через MsgBox со ссылкой на столбец A. Поскольку мы упоминали столбец A в функции Range при определении формулы переменной.
Шаг 8: Если мы удалим строку и сможем запустить эту формулу. Давай посмотрим что происходит.
Вы можете видеть, что система все еще дала счетчик строк как 14. Несмотря на то, что я удалил строку, а фактическое количество строк равно 13, система точно не зафиксировала количество строк. Чтобы система зафиксировала фактическое количество строк, вам нужно сохранить лист и снова запустить код.
Вы можете видеть фактическое количество строк, показанное на этом снимке экрана.
Выполните следующие шаги, чтобы получить последнюю непустую строку в Excel, используя код VBA:
Шаг 1: Определите переменную как долго.
Шаг 2: Теперь используйте следующий код, чтобы увидеть последнюю непустую строку.
Шаг 3: Используйте MsgBox, чтобы сохранить значение последней непустой строки и увидеть его как всплывающее окно.
Шаг 4: Запустите код и просмотрите вывод в виде всплывающего окна, содержащего последний непустой номер строки.
Выход:
То, что нужно запомнить
Рекомендуемые статьи
Как определить последнюю строку в VBA, включая пробелы между
Как определить последнюю строку на листе Excel, включая некоторые пустые строки посередине?
С помощью этой функции:
функция возвращает 5, и мне нужно 13. Есть идеи, как это сделать?
11 ответов
вы действительно близки. Попробуйте использовать большой номер строки с End(xlUp)
лучший способ получить номер последней строки используется:
ActiveSheet.UsedRange.Rows.Count на текущий лист.
Worksheets(«Sheet name»).UsedRange.Rows.Count на конкретный лист.
получить номер последнего столбца:
ActiveSheet.UsedRange.Columns.Count на текущий лист.
Worksheets(«Sheet name»).UsedRange.Columns.Count на конкретный лист.
надеюсь, это поможет.
Я использую следующий:
Он найдет последнюю строку в определенном столбце. Если вы хотите последнюю использованную строку любой затем:
короче. Безопасный. Быстрый. Вернет последнюю непустую строку, даже если есть пустые строки поверх листа или где-либо еще. Работает также для пустого листа (Excel reports 1 использовал строку на пустом листе, поэтому выражение будет 1). Протестировано и работает на Excel 2002 и Excel 2010.
проблема заключается в «As string» в вашей функции. Замените его на «как двойной «или» как долго», чтобы он работал. Таким образом, он даже работает, если последняя строка больше, чем «большое число», предложенное в принятом ответе.
Так что это должно работать
помимо всех упомянутых, есть несколько других способов найти последнюю строку или столбец на листе или в указанном диапазоне.
Примечание: пожалуйста, используйте только один из вышеперечисленных методов, поскольку он оправдывает ваше заявление о проблеме.
Пожалуйста, обратите внимание на то, что метод Find не видит форматирование ячейки но только данные, поэтому ищите xlCellTypeLastCell, если важны только данные, а не форматирование. Кроме того,слился ячейки (которых следует избегать) могут дать вам неожиданные результаты поскольку он даст вам номер строки первой ячейки, а не последней ячейки в Объединенных ячейках.
ActiveSheet можно заменить на WorkSheets(1) или WorkSheets(«name here»)
Если лист содержит неиспользуемую область сверху, UsedRange.Rows.Count не является максимальной строкой.
это правильный номер строки max.
вот мой код, чтобы найти следующую пустую строку, например, в первой строке «A». Чтобы использовать его для любой другой строки, просто измените ячейки (i,2 или 3 или 4 и так далее)