Как найти последнюю заполненную строку эксель

Найдем номер строки последней заполненной ячейки в столбце и списке. По номеру строки найдем и само значение.

Рассмотрим диапазон значений, в который регулярно заносятся новые данные.

Диапазон без пропусков и начиная с первой строки

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

=СЧЁТЗ(A:A))

Формула работает для числовых и текстовых диапазонов (см.

Файл примера

)

Значение из последней заполненной ячейки в столбце выведем с помощью функции

ИНДЕКС()

:

=ИНДЕКС(A:A;СЧЁТЗ(A:A))

Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так:

=ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))

Диапазон без пропусков в любом месте листа

Если список, в который вводятся значения расположен в диапазоне

E8:E30

(т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом:

=СЧЁТЗ(E9:E30)+СТРОКА(E8)

Формула

СТРОКА(E8)

возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции

ИНДЕКС()

:

=ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))

Диапазон с пропусками (числа)

В случае

наличия пропусков

(пустых строк) в столбце, функция

СЧЕТЗ()

будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает

пустые

ячейки.

Если диапазон заполняется

числовыми

значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу

=ПОИСКПОЗ(1E+306;A:A;1)

. Пустые ячейки и текстовые значения игнорируются.

Так как в качестве просматриваемого массива указан целый столбец (

A:A

), то функция

ПОИСКПОЗ()

вернет номер последней заполненной строки. Функция

ПОИСКПОЗ()

(с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был

отсортирован

по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.

Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне

A2:A20

, можно использовать формулу:

=ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

Диапазон с пропусками (текст)

В случае необходимости определения номера строки последнего

текстового

значения (также при наличии пропусков), формулу нужно переделать:

=ПОИСКПОЗ(«*»;$A:$A;-1)

Пустые ячейки, числа и текстовое значение

Пустой текст

(«») игнорируются.

Диапазон с пропусками (текст и числа)

Если столбец содержит и

текстовые и числовые значения

, то для определения номера строки последней заполненной ячейки можно предложить универсальное решение:

=МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))

Функция

ЕСЛИОШИБКА()

нужна для подавления ошибки возникающей, если столбец

A

содержит только текстовые или только числовые значения.

Другим универсальным решением является

формула массива

:

=МАКС(СТРОКА(A1:A20)*(A1:A20<>»»))

Или

=МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20)))

После ввода

формулы массива

нужно нажать

CTRL + SHIFT + ENTER

. Предполагается, что значения вводятся в диапазон

A1:A20

. Лучше задать фиксированный диапазон для поиска, т.к. использование в

формулах массива

ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции

ДВССЫЛ()

:

=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>»»)))

Или

=ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*НЕ(ЕПУСТО(A1:A20))))

Как обычно, после ввода

формулы массива

нужно нажать

CTRL + SHIFT + ENTER

вместо

ENTER

.

СОВЕТ:

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

Советы по построению таблиц

.

При составлении формул в Excel часто возникает необходимость найти последнюю строку или получить последнее значение в столбце таблицы с данными. Здесь следует учитывать несколько условий, поставленных перед поиском: будет ли список значений в столбце неразрывным или содержать пустые ячейки? Какие это значения: текст, числа? От этих факторов зависит тип используемых формул.

Как найти последнюю заполненную строку в столбце таблицы Excel

Ниже на рисунке представлен неотсортированный список фактур. Допустим нам необходимо найти последнюю строку с фактурой в списке номеров фактур. Простым способом поиска последней позиции в столбце является использование функции ИНДЕКС и подсчет всех позиций списка с целью определения номера последней строки.

Функция ИНДЕКС использована с одним столбцом требует лишь указать один аргумент с номером строки. Третий необязательный для заполнения аргумент в данной ситуации не используется. Функция СЧЁТЗ используется с целью подсчитывания непустых ячеек в столбце B. Ее итоговый результат вычисления следует увеличить на число +1, так как в первой строке пустая ячейка. Функция ИНДЕКС в данном примере возвращает 12-ую строку в столбце B.

Функция СЧЁТЗ подсчитывает ячейки содержащие значения: числа, текстовые строки, даты и все любые другие значения за исключением пустых ячеек. Если ваши данные содержат пустые ячейки, которые разрывают целостность списков, тогда эта формула не будет возвращать правильных результатов вычисления.



Поиск последнего числа в столбце с пустыми ячейками Excel

Функции ИНДЕКС и СЧЁТЗ прекрасно используются для поиска значений в случае, когда диапазон исходных данных не содержит пустых ячеек и является неразрывным. Если же исходных диапазон ячеек содержит пустые ячейки, а искомыми значениями являются числа, можно воспользоваться функцией ПРОСМОТР с очень большим числовым значением в аргументах. Данная техника применяется с помощью составления следующей формулы:

Искомое значение в данной формуле (единица с 308 знаками) – это самое большое число доступное в программе Excel. Так как функция ПРОСМОТР не имела шансов найти большего значения чем искомое, она прекратила свое вычисление на последнем найденном числовом значении, которую и вернула в результат.

Что значит число с буквой E в Excel?

Число такое как, например, 9,99E+307 записано в экспоненциальном формате. Число перед буквой E имеет одну цифру 0-9 и только две цифры после запятой. Число после буквы E значит количество разрядов, на которые следует сместить запятую (307 в данном примере), чтобы получить числовое значение, записанное традиционным способом. Плюс значит, что запятую следует смещать в право, а минус – влево. Например, запись 4,32E-02 означает число, записанное в десятичной дроби 0,0432.

Функция ПРОСМОТР имеет преимущество перед другими функциями в том, что она возвращает последнее число даже тогда, когда ячейки в просматриваемом диапазоне могут быть не только пустыми, но и содержать текстовые строки, коды ошибок или даты.

Итог: узнаете, как найти последнюю строку, столбец или ячейку в таблице с использованием трех различных методов на VBA. Используемый метод зависит от макета данных и от того, содержит ли лист пустые ячейки.

Уровень мастерства: средний

Видео: 3 части серии как найти последнюю ячейку с VBA

Видео лучше всего просматривать в полноэкранном режиме HD

Загрузите файл, содержащий код:

Find Last Cell VBA Примеры.xlsm (79.6 KB)

Поиск последней ячейки — все о данных

Поиск последней используемой строки, столбца или ячейки — это одна из самых распространенных задач при написании макросов и приложений VBA. Как и все в Excel и VBA, есть много различных способов.

Выбор правильного метода в основном зависит от того, как выглядят ваши данные.

В этой статье я объясняю три различных метода VBA объекта Range, которые мы можем использовать для поиска последней ячейки на листе. Каждый из этих методов имеет плюсы и минусы, а некоторые выглядят страшнее других. 🙂

Но понимание того, как работает каждый метод, поможет вам узнать, когда их использовать и почему.

#1 – The Range.End() Method

Range.End() очень похож на сочетание клавиш Ctrl+Arrow. В VBA можно использовать этот метод, чтобы найти последнюю не пустую ячейку в одной строке или столбце.

Диапазон.Пример кода End VBA

Sub Последняя_ячейка()
'Найти последнюю не пустую ячейку в одной строке или столбце

Dim lRow As Long
Dim lCol As Long
    
    'Найти последнюю не пустую ячейку в столбце А(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Найти последнюю непустую ячейку в строке 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    MsgBox "Последняя строка: " & lRow & vbNewLine & _
            "Последний столбец: " & lCol
  
End Sub

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

Оператор Rows.Count возвращает количество всех строк на рабочем листе. Поэтому мы в основном указываем последнюю ячейку в столбце A листа (ячейка A1048567) и поднимаемся до тех пор, пока не найдем первую непустую ячейку.

Это работает так же с поиском последнего столбца. Он начинается с последнего столбца в строке, затем идет влево, пока в столбце не будет найдена последняя непустая ячейка. Columns.Count возвращает общее количество столбцов на листе. Итак, мы начинаем с последнего столбца и идем налево.

Аргумент для метода End указывает, в каком направлении идти.
Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.

Плюсы Range.End

• Range.End прост в использовании и понимании, так как он работает так же, как сочетания клавиш Ctrl+Arrow.
• Может использоваться для поиска первой пустой ячейки или последней непустой ячейки в одной строке или столбце.

Минусы Range.End

• Range.End работает только с одной строкой или столбцом. При наличии диапазона данных, содержащего пробелы в последней строке или столбце, может быть трудно определить, в какой строке или столбце выполнять метод.
• Если вы хотите найти последнюю используемую ячейку, то вы должны оценить по крайней мере два оператора. Один, чтобы найти последнюю строку и один, чтобы найти последний столбец. Затем их можно объединить для ссылки на последнюю ячейку.

Вот справочные статьи для Range.End

• Страница справки MSDN для Range.End
• Справка MSDN для перечислений xlDirection

#2 – The Range.Find() Method

Метод Range.Find — я предпочитаю этот способ, чтобы найти последнюю строку, столбец или ячейку. Он самый универсальный, но и самый страшный.

У Range.Find много аргументов, но пусть это вас не пугает. Когда вы знаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.

Range.Find — это в основном способ программирования меню «Find» в Excel. Он делает то же самое, и большинство аргументов Range.Find являются опциями в меню Find.

Пример кода Range.Find

Ниже
приведен код для поиска последней непустой строки.            

Sub Последняя_ячейка_Find()
'Находит последнюю непустую ячейку на листе / диапазоне.

Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    MsgBox "Последняя строка: " & lRow

End Sub

Метод Range.Find. Пояснения

Метод Find ищет первую непустую ячейку («*»). Звездочка представляет
собой символ подстановки, который ищет любой текст или числа в ячейке.

Начиная с ячейки A1, он перемещается назад (xlPrevious) и
фактически начинает поиск в самой последней ячейке на листе. Затем он
перемещается справа налево (xlByRows) и проходит по каждой строке, пока не
найдет непустую ячейку. При обнаружении непустого он останавливается и
возвращает номер строки.

Вот подробное объяснение каждого аргумента.

• What:=”*” — звездочка — это символ подстановки, который находит любой текст или число в ячейке. Это в основном то же самое, что и поиск непустой ячейки.
• After:=Range(“A1”)— начать поиск после ячейки А1, первой ячейки на листе. Это означает, что A1 не будет искать. Поиск начнется после A1, и следующая ячейка, которую он ищет, зависит от SearchOrder и SearchDirection. Этот аргумент можно изменить, чтобы он начинался в другой ячейке, просто помните, что поиск фактически начинается в ячейке после указанной.
• LookAt: = xlPart — это будет смотреть на любую часть текста внутри ячейки. Другой вариант — xlWhole, который будет пытаться соответствовать всему содержимому ячейки.
• LookIn: = xlFormulas — Это заставляет Find искать в формулах, и это важный аргумент. Другой вариант — xlValues, который будет искать только значения. Если у вас есть формулы, которые возвращают пробелы (= IF (A2> 5, «Ok», «»), то вы можете считать это непустой ячейкой. При указании LookIn в качестве xlFormulas эта формула будет считаться непустой, даже если возвращаемое значение пустое.
• SearchOrder: = xlByRows — это говорит Find, чтобы искать через каждую целую строку прежде, чем перейти к следующей. Направление поиска слева направо или справа налево зависит от аргумента SearchDirection. Другой вариант здесь — xlByColumns, который используется при поиске последнего столбца.
• SearchDirection: = xlPrevious — указывает направление поиска. xlPrevious означает, что он будет искать справа налево или снизу вверх. Другой вариант — xlNext, который перемещается в противоположном направлении.
• MatchCase: = False — это говорит Find, чтобы не рассматривать заглавные или строчные буквы. Если установить значение True, это поможет. Этот аргумент не является необходимым для этого сценария.

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

Плюсы
Range.Find

• Find ищет во всем диапазоне последнюю непустую строку или столбец. Он НЕ ограничен одной строкой или столбцом.
• Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
• Аргументы могут использоваться для поиска в разных направлениях и для определенных значений, а не только пустых ячеек.

Минусы Range.Find

• Это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (Что), вы должны привыкнуть использовать хотя бы первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать ваши последние использованные настройки в окне поиска. Это важно. Если вы не укажете необязательные аргументы для LookAt, LookIn и SearchOrder, тогда метод Find будет использовать те параметры, которые вы использовали последними в окне поиска Excel.
• Нахождение последней ячейки требует двух утверждений. Один, чтобы найти последний ряд и один, чтобы найти последний столбец. Затем вы должны объединить их, чтобы найти последнюю ячейку.

Macro Recorder выручит!

Range.Find — все еще мой любимый метод для нахождения последней ячейки из-за ее универсальности. Но нужно много напечатать и запомнить. К счастью, вам это не нужно.

Вы можете использовать макро рекордер, чтобы быстро создать код со всеми аргументами.

1. Запустить макро рекордер
2. Нажмите Ctrl + F
3. Затем нажмите кнопку «Найти далее»

Код для метода Find со всеми аргументами будет сгенерирован устройством записи макросов.

Используйте пользовательскую функцию для метода Find

Вы также можете использовать пользовательскую функцию (UDF) для метода поиска. Последняя функция Ron de Bruin — прекрасный пример. Вы можете скопировать эту функцию в любой проект или модуль кода VBA и использовать ее для возврата последней строки, столбца или ячейки.

У меня также есть аналогичная функция в примере рабочей книги. Моя функция просто имеет дополнительные аргументы для ссылки на лист и диапазон для поиска.

Вот справочные статьи для Range.Find

• MSDN Help Article for Range.Find Method

#3 – Range.SpecialCells (xlCellTypeLastCell)

Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.

Пример кода SpecialCells (xlCellTypeLastCell)

Sub Range_SpecialCells_Method()

    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
      
End Sub

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

Часто вы будете нажимать Ctrl + End на клавиатуре и попадете в какую-нибудь ячейку вниз в конце листа, который определенно не используется. Это может произойти по ряду причин. Одной из распространенных причин является то, что свойства форматирования для этой ячейки были изменены. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как использованную ячейку.

Плюсы Range.SpecialCells

• Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может позволить вам быстро определить, существуют ли какие-либо неиспользуемые строки или столбцы на листе.
• Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.

Минусы Range.SpecialCells

• Excel только сбрасывает последнюю ячейку при сохранении книги. Поэтому, если пользователь или макрос удаляет содержимое некоторых ячеек, этот метод не найдет истинную последнюю ячейку, пока файл не будет сохранен.
• Он находит последнюю использованную ячейку, а НЕ последнюю непустую ячейку.

Другие методы поиска последней ячейки

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

У меня также есть статья о том, как найти ПЕРВУЮ ячейку в листе.

Пожалуйста, оставьте комментарий ниже, если у вас есть какие-либо вопросы, или вы все еще не можете найти последнюю ячейку. Я буду рад помочь!