Как узнать номер последней строки в excel
Обновлено: 03.07.2024
Очень часто при внесении данных на лист 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 не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.
Переменную, которой присваивается номер последней строки, следует объявлять как 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 часто возникает необходимость найти последнюю строку или получить последнее значение в столбце таблицы с данными. Здесь следует учитывать несколько условий, поставленных перед поиском: будет ли список значений в столбце неразрывным или содержать пустые ячейки? Какие это значения: текст, числа? От этих факторов зависит тип используемых формул.
Как найти последнюю заполненную строку в столбце таблицы Excel
Ниже на рисунке представлен неотсортированный список фактур. Допустим нам необходимо найти последнюю строку с фактурой в списке номеров фактур. Простым способом поиска последней позиции в столбце является использование функции ИНДЕКС и подсчет всех позиций списка с целью определения номера последней строки.
Функция ИНДЕКС использована с одним столбцом требует лишь указать один аргумент с номером строки. Третий необязательный для заполнения аргумент в данной ситуации не используется. Функция СЧЁТЗ используется с целью подсчитывания непустых ячеек в столбце B. Ее итоговый результат вычисления следует увеличить на число +1, так как в первой строке пустая ячейка. Функция ИНДЕКС в данном примере возвращает 12-ую строку в столбце B.
Функция СЧЁТЗ подсчитывает ячейки содержащие значения: числа, текстовые строки, даты и все любые другие значения за исключением пустых ячеек. Если ваши данные содержат пустые ячейки, которые разрывают целостность списков, тогда эта формула не будет возвращать правильных результатов вычисления.
Поиск последнего числа в столбце с пустыми ячейками Excel
Функции ИНДЕКС и СЧЁТЗ прекрасно используются для поиска значений в случае, когда диапазон исходных данных не содержит пустых ячеек и является неразрывным. Если же исходных диапазон ячеек содержит пустые ячейки, а искомыми значениями являются числа, можно воспользоваться функцией ПРОСМОТР с очень большим числовым значением в аргументах. Данная техника применяется с помощью составления следующей формулы:
Искомое значение в данной формуле (единица с 308 знаками) – это самое большое число доступное в программе Excel. Так как функция ПРОСМОТР не имела шансов найти большего значения чем искомое, она прекратила свое вычисление на последнем найденном числовом значении, которую и вернула в результат.
Что значит число с буквой E в Excel?
Число такое как, например, 9,99E+307 записано в экспоненциальном формате. Число перед буквой E имеет одну цифру 0-9 и только две цифры после запятой. Число после буквы E значит количество разрядов, на которые следует сместить запятую (307 в данном примере), чтобы получить числовое значение, записанное традиционным способом. Плюс значит, что запятую следует смещать в право, а минус – влево. Например, запись 4,32E-02 означает число, записанное в десятичной дроби 0,0432.
Функция ПРОСМОТР имеет преимущество перед другими функциями в том, что она возвращает последнее число даже тогда, когда ячейки в просматриваемом диапазоне могут быть не только пустыми, но и содержать текстовые строки, коды ошибок или даты.
= Мир MS Excel/Статьи об Excel
| Приёмы работы с книгами, листами, диапазонами, ячейками [6] |
| Приёмы работы с формулами [13] |
| Настройки Excel [3] |
| Инструменты Excel [4] |
| Интеграция Excel с другими приложениями [4] |
| Форматирование [1] |
| Выпадающие списки [2] |
| Примечания [1] |
| Сводные таблицы [1] |
| Гиперссылки [1] |
| Excel и интернет [1] |
| Excel для Windows и Excel для Mac OS [2] |
Если Вам необходимо в таблицах, которые имеют неодинаковое количество ячеек в строках и/или столбцах, например таких:
находить последние заполненные ячейки и извлекать из них значения, то в Excel Вы, к сожалению, не найдёте функции типа ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()
Вот как это сделать имеющейся в стандартном наборе функций функцией ПРОСМОТР().
1. Для текстовых значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет сверху вниз в указанном столбце текст "яяя" и не найдя его, останавливается на последней ячейке в которой есть хоть какой-то текст. Так как мы не указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из второго аргумента "Вектор_просмотра".
Пояснение: Почему именно "яяя"? Во-первых, потому что функция сравнивает при поиске текст посимвольно, а символ "я" в русском языке последний и все предыдущие при сравнении отбрасываются, во-вторых, потому что в русском языке нет такого слова.
Примечание: Вообще-то достаточно использовать и "яя", но тогда возникает мизерная возможность попасть на таблицу, в которой будет такое слово. Так называются город и река в Кемеровской области. В детстве я был в этом городе и даже купался в этой реке :)
2. Для числовых значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число "9E+307" и не найдя его, останавливается на последней ячейке в которой есть хоть какое-то число. Так как мы не указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из второго аргумента "Вектор_просмотра".
Пояснение: Почему именно "9E+307"? Потому что это максимально возможное число в Excel. Поэтому функция найти его может только в каком-то невероятном случае, в реальной жизни пользователь такими числами просто не оперирует.
3. Для смешанных (текстово-числовых) значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число "1" и найдя его, останавливается на последней ячейке в которой есть это число. Так как мы указали третий аргумент этой функции "Вектор_результатов", то функция возвращает значение из него, соответствующее позиции последнего вхождения искомого в просматриваемый массив.
Пояснение: Почему именно "1"? Да просто так :) С таким же успехом можно использовать число 2 или 3 или 100500, например. Главное что бы первый аргумент функции был не менее делимого в выражении 1/Диапазон. Вот пример применения другого числа в первом аргументе, при делимом отличном от единицы:
Читайте также: