Как найти последнюю заполненную ячейку в excel

Поиск и сброс последней ячейки на листе

При этом в Excel сохраняется только часть каждого из них, которая содержит данные или форматирование. Пустые ячейки могут содержать форматирование, из-за котором последняя ячейка в строке или столбце выпадет за пределы диапазона ячеек, содержащих данные. В результате размер файла книги будет больше, чем требуется, и при печати книги на печать может потребоваться больше страниц.

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

Поиск последней ячейки с данными или форматированием на нем

• Чтобы найти последнюю ячейку с данными или форматированием, щелкните в любом месте на нем и нажмите CTRL+END.

Примечание: Чтобы выбрать последнюю ячейку в строке или столбце, нажмите клавишу END, а затем клавишу СТРЕЛКА ВПРАВО или СТРЕЛКА ВНИЗ.

Очистка всего форматирования между последней ячейкой и данными

1. Выполните одно из указанных ниже действий.
2. Чтобы выбрать все столбцы справа от последнего столбца с данными, щелкните первый заголовок столбца, нажмите и удерживайте нажатой кнопку CTRL, а затем щелкните заголовки столбцов, которые нужно выбрать.

Совет: Можно также щелкнуть первый заголовок столбца и нажать CTRL+SHIFT+END.
Совет: Можно также щелкнуть заголовок первой строки и нажать CTRL+SHIFT+END.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

c# excel определить последнюю ячейку

получил, как и положено, lastRow = 28030; Затем в этом файле вручную удалил нижнюю часть ячеек, осталось 28 007. Все так же идущие подряд. Используя все те же строчки кода, lastRow = 28016; Пробовал несколько раз удалять (и добавлять больше, чем было) ячейки. При удалении/добавлении иногда последнюю определяет правильно, иногда нет. В других аналогичных файлах ситуация повторяется. Как же 100% правильно определить нижнюю не пустую ячейку?

Отслеживать
задан 5 апр 2018 в 16:18
157 2 2 серебряных знака 12 12 бронзовых знаков

1 ответ 1

Сортировка: Сброс на вариант по умолчанию

Ответ по операторам VBA-Excel. Похоже, рекомендации применимы и к этому вопросу.

UsedRange — пользовательский диапазон. Но он может начинаться не с первой строки на листе и заканчиваться не последней строкой с даными. Это диапазон, в котором производились какие-либо действия.

На новом листе в строке 5 рисуем шапку таблицы, вносим 10 строк данных. В данном случае UsedRange.Rows.Count = 11 (10 строк + заголовки).

Удаляем данные из последней строки. Диапазон не изменился — последняя строка в диапазоне пользователя.

Удаляем последнюю строку таблицы. И? Нет, в диапазоне пользователя по прежднему 11 строк. А вот если после удаления строки сохранить изменения — UsedRange.Rows.Count = 10. Наконец-то избавились. Если же сохраниться после удаления данных, то не факт, что диапазон уменьшится — ячейки отформатированы.

Еще ситуация. Копирование из другой таблицы. Копируют не глядя — Copy/Paste целыми столбцами. И часто-густо книга с данными в сотню строк имеет мегабайтный вес. Причина? Копировалось все, в том числе и форматы нижних строк, в которых данных нет, но с которыми раньше усиленно работали.

Последняя рабочая строка на листе с учетом возможных незадействованных строк сверху:

With excelworksheet LastRow = .UsedRange.Rows.Count + .UsedRange.Row - 1 End With 

Для примера с таблицей: 11 + 5 — 1 = 15 Следует помнить, что будут учтены и форматированные строки (вернее: строки с форматированными ячейками) без данных.

Последнюю строку с данными можно определять другим способом:

With excelworksheet LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row End With 

Здесь единичка — столбец А (можно писать имя столбца — «A»).

Этот метод тоже имеет недостаток. Так определится последняя ячейка с данными, но в отображаемых строках. Если на листе применен фильтр и посление строки с данным скрыты, LastRow покажет меньшее число.

Если заранее неизвестно, будут ли пустые форматированные ниже диапазона с данными и будут ли скрытые строки, можно задействовать один из вариантов:

• определить нижнюю строку UsedRange, в цикле проверить наличие данных и найти последнюю заполненную ячейку;
• принудительно отобразить все строки и применить End(xlUp).Row

Как найти последнюю заполненную ячейку в excel

Запись: xintrea/mytetra_db_adgaver_new/master/base/1514989712v0ghmn03cl/text.html на raw.githubusercontent.com

Как определить последнюю ячейку на листе через VBA?

Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.

В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow . Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767 (это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить

Одинаковые переменные для всех примеров

D im lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long

Dim lLastRow As Long

‘а для lLastCol можно применить тип Integer,

‘т.к. столбцов в Excel пока меньше 32767

Dim lLastCol As Long

Способ 1:
Определение последней заполненной строки через свойство End

l LastRow = Cells(Rows.Count,1).End(xlUp).Row

определяя таким способом нам надо знать что:
1 — это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.

Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце (или будет заполнена самая последняя ячейка столбца) — то результат будет неверный (ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End

l LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

1 — это номер строки, последнюю заполненную ячейку в которой мы определяем.

Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

Способ 2:
Определение последней заполненной строки через SpecialCells

l LastRow = Cells.SpecialCells(xlLastCell).Row

Определение последнего столбца через SpecialCells

l LastCol = Cells.SpecialCells(xlLastCell).Column

Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку (строку — Row либо столбец — Column ) . Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование (например, заливку) , но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).

Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.

Способ 3:
Определение последней строки через UsedRange

l LastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

Определение последнего столбца через UsedRange

l LastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

• ActiveSheet.UsedRange.Row — этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это — если у вас первые строк 5 не заполнены ничем, то данная строка вернет 6 (т.е. номер первой строки с данными) . Если же все строки заполнены — то вернет 1 .
• ActiveSheet.UsedRange.Rows.Count — определяем кол-во строк, входящих в весь диапазон данных на листе.
  Т.е. получается: первая строка данных + кол-во строк с данными — 1. Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 3 . Всего строк: 3 . 3 + 3 = 6. Вроде все верно, чего тут непонятного? А теперь выделите на листе три ячейки, начиная с 3-ей. Все верно. Ведь у нас в 3-ей строке уже есть данные. Думаю, остальное уже понятно и без моих пояснений.
• То же самое и с ActiveSheet.UsedRange.Column , только уже не для строк, а для столбцов.

Обладает всеми недостатками предыдущего метода. . Однако, можно перед определением последней строки/столбца записать строку: With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.

Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая — следующая за ней. Т.е. к результату необходимо прибавить 1.

Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find

D im rF As Range Dim lLastRow As Long, lLastCol As Long ‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find(«*», , xlValues, xlWhole, xlPrevious) If Not rF Is Nothing Then lLastRow = rF.Row ‘последняя заполненная строка lLastCol = rF.Column ‘последний заполненный столбец MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки Else ‘если ничего не нашлось — значит лист пустой ‘и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 End If

Dim rF As Range

Dim lLastRow As Long, lLastCol As Long

‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение

Set rF = ActiveSheet.UsedRange.Find(«*», , xlValues, xlWhole, xlPrevious)

If Not rF Is Nothing Then

lLastRow = rF.Row ‘последняя заполненная строка

lLastCol = rF.Column ‘последний заполненный столбец

MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки

‘если ничего не нашлось — значит лист пустой

‘и можно назначить в качестве последних первую строку и столбец

Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул — только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=»»), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.

Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы . Это следует учитывать при его применении.

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

S ub 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

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

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

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

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

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

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

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

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

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

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

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

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры