vba номер последней заполненной строки
Последнее значение в столбце Excel
Итог: узнаете, как найти последнюю строку, столбец или ячейку в таблице с использованием трех различных методов на VBA. Используемый метод зависит от макета данных и от того, содержит ли лист пустые ячейки.
Уровень мастерства: средний
Видео: 3 части серии как найти последнюю ячейку с VBA
Видео лучше всего просматривать в полноэкранном режиме HD
Загрузите файл, содержащий код:
Поиск последней ячейки — все о данных
Поиск последней используемой строки, столбца или ячейки — это одна из самых распространенных задач при написании макросов и приложений VBA. Как и все в Excel и VBA, есть много различных способов.
Выбор правильного метода в основном зависит от того, как выглядят ваши данные.
В этой статье я объясняю три различных метода VBA объекта Range, которые мы можем использовать для поиска последней ячейки на листе. Каждый из этих методов имеет плюсы и минусы, а некоторые выглядят страшнее других. 🙂
Но понимание того, как работает каждый метод, поможет вам узнать, когда их использовать и почему.
#1 – The Range.End() Method
Range.End() очень похож на сочетание клавиш Ctrl+Arrow. В VBA можно использовать этот метод, чтобы найти последнюю не пустую ячейку в одной строке или столбце.
Диапазон.Пример кода End VBA
Чтобы найти последнюю использованную строку в столбце, этот метод начинается с последней ячейки столбца и идет вверх (xlUp), пока не найдет первую непустую ячейку.
Оператор Rows.Count возвращает количество всех строк на рабочем листе. Поэтому мы в основном указываем последнюю ячейку в столбце A листа (ячейка A1048567) и поднимаемся до тех пор, пока не найдем первую непустую ячейку.
Это работает так же с поиском последнего столбца. Он начинается с последнего столбца в строке, затем идет влево, пока в столбце не будет найдена последняя непустая ячейка. Columns.Count возвращает общее количество столбцов на листе. Итак, мы начинаем с последнего столбца и идем налево.
Аргумент для метода End указывает, в каком направлении идти. Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.
Плюсы Range.End
Минусы Range.End
Вот справочные статьи для Range.End
#2 – The Range.Find() Method
Метод Range.Find — я предпочитаю этот способ, чтобы найти последнюю строку, столбец или ячейку. Он самый универсальный, но и самый страшный.
У Range.Find много аргументов, но пусть это вас не пугает. Когда вы знаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.
Range.Find — это в основном способ программирования меню «Find» в Excel. Он делает то же самое, и большинство аргументов Range.Find являются опциями в меню Find.
Пример кода Range.Find
Ниже приведен код для поиска последней непустой строки.
Метод Range.Find. Пояснения
Метод Find ищет первую непустую ячейку («*»). Звездочка представляет собой символ подстановки, который ищет любой текст или числа в ячейке.
Начиная с ячейки A1, он перемещается назад (xlPrevious) и фактически начинает поиск в самой последней ячейке на листе. Затем он перемещается справа налево (xlByRows) и проходит по каждой строке, пока не найдет непустую ячейку. При обнаружении непустого он останавливается и возвращает номер строки.
Вот подробное объяснение каждого аргумента.
Да, я знаю, что это много, но надеюсь, у вас будет лучшее понимание того, как использовать эти аргументы, чтобы найти что-нибудь на листе.
Плюсы Range.Find
Минусы Range.Find
Macro Recorder выручит!
Range.Find — все еще мой любимый метод для нахождения последней ячейки из-за ее универсальности. Но нужно много напечатать и запомнить. К счастью, вам это не нужно.
Вы можете использовать макро рекордер, чтобы быстро создать код со всеми аргументами.
Код для метода Find со всеми аргументами будет сгенерирован устройством записи макросов.
Используйте пользовательскую функцию для метода Find
Вы также можете использовать пользовательскую функцию (UDF) для метода поиска. Последняя функция Ron de Bruin — прекрасный пример. Вы можете скопировать эту функцию в любой проект или модуль кода VBA и использовать ее для возврата последней строки, столбца или ячейки.
У меня также есть аналогичная функция в примере рабочей книги. Моя функция просто имеет дополнительные аргументы для ссылки на лист и диапазон для поиска.
Вот справочные статьи для Range.Find
#3 – Range.SpecialCells (xlCellTypeLastCell)
Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.
Пример кода SpecialCells (xlCellTypeLastCell)
На самом деле это самый простой способ найти последнюю использованную ячейку. Однако этот метод находит последнюю использованную ячейку, которая может отличаться от последней непустой ячейки.
Часто вы будете нажимать Ctrl + End на клавиатуре и попадете в какую-нибудь ячейку вниз в конце листа, который определенно не используется. Это может произойти по ряду причин. Одной из распространенных причин является то, что свойства форматирования для этой ячейки были изменены. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как использованную ячейку.
Плюсы Range.SpecialCells
Минусы Range.SpecialCells
Другие методы поиска последней ячейки
Что ж, это должно охватывать основы поиска последней использованной или непустой ячейки на листе. Если ваш лист содержит объекты (таблицы, диаграммы, сводные таблицы, слайсеры и т. Д.), Вам может потребоваться использовать другие методы для поиска последней ячейки. Я объясню эти методы в отдельном посте.
У меня также есть статья о том, как найти ПЕРВУЮ ячейку в листе.
Пожалуйста, оставьте комментарий ниже, если у вас есть какие-либо вопросы, или вы все еще не можете найти последнюю ячейку. Я буду рад помочь!
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. Номер последней заполненной строки
Поиск номера последней заполненной строки с помощью кода 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, включая пробелы между
Как определить последнюю строку на листе 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 и так далее)
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: Запустите код и просмотрите вывод в виде всплывающего окна, содержащего последний непустой номер строки.
Выход:
То, что нужно запомнить
Рекомендуемые статьи
- во что можно вложить 100 тысяч рублей чтобы заработать
- все что было закружило как пожелтевшую листву песня