0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как вернуть номер строки первой или последней непустой ячейки на листе?

excel-vba
Методы поиска последней использованной строки или столбца на листе

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

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

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

Также не будут включаться строки с непустыми ячейками.

Чтобы устранить указанные выше ограничения, строка:
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row

Может быть заменено на :

для последнего использованного ряда "Sheet1" :
LastRow = wS.UsedRange.Row — 1 + wS.UsedRange.Rows.Count .

для последней непустой ячейки столбца "A" в "Sheet1" :

Найти последнюю строку с использованием именованного диапазона

Если у вас есть именованный диапазон в листе и вы хотите динамически получить последнюю строку этого динамически именованного диапазона. Включены случаи, когда именованный диапазон начинается не с первой строки.

Обновить:
Потенциальная лазейка была отмечена @Jeeped для aa named range с несмежными строками, поскольку она генерирует неожиданный результат. Чтобы решить эту проблему, код пересматривается, как показано ниже.
Апппции: таргеты sheet = form , named range = MyNameRange

Получить строку последней ячейки в диапазоне

Найти последнюю непустую колонку в рабочем листе

Если вы хотите узнать, нет ли на листе данных, выберите один из этих двух вариантов:

  • NO: использовать LastCol_1: вы можете использовать его непосредственно в wS.Cells(. LastCol_1(wS))
  • YES: использовать LastCol_0: перед использованием функции проверить, равен ли результат, полученный функцией, 0 или нет.

Свойства объекта Err автоматически сбрасываются по завершении функции.

Последняя ячейка в Range.CurrentRegion

Range.CurrentRegion — прямоугольная область диапазона, окруженная пустыми ячейками. Пустые ячейки с такими формулами, как ="" или ' , не считаются пустыми (даже ISBLANK Excel).

Читайте так же:
Как автоматически отображать значение по умолчанию при удалении значения в раскрывающемся списке в Excel?

Найти последнюю непустую строку в рабочем листе

Если вы хотите узнать, не содержит ли рабочий лист каких-либо данных, доступны следующие опции:

  • НЕТ: Использовать LastRow_1: вы можете использовать ее непосредственно в wS.Cells(LastRow_1(wS.) )
  • ДА: Использовать LastRow_0: вы должны проверить, равен ли результат функции 0 или нет, прежде чем использовать ее.

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

В данном примере мы рассмотрим, как найти первый непустой столбец строки.

Метод будет работать независимо от пустых областей набора данных.

Если задействованы слившиеся ячейки, метод End будет «остановлен» против слившейся области и вернет первую ячейку в слившейся области.

Кроме того, непустые ячейки в скрытых столбцах не учитываются.

Найти последнюю непустую ячейку в рабочем листе — Производительность (массив)

  • Первая функция, использующая массив, намного быстрее
  • Если .ThisWorkbook.ActiveSheet не имеет дополнительных параметров, то по умолчанию используется .ThisWorkbook.ActiveSheet
  • Если диапазон пуст, то по умолчанию используется Cell( 1, 1), а не Nothing.

GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds

. Измеряется с помощью микротаймера

Последняя заполненная ячейка в EXCEL

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

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

Если вы вводите значения в столбец, начиная с первой строки и без пробелов, вы можете вычислить строку последней заполненной ячейки, посмотрев формулу: =CHOTZ(A:A)).

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

Получите значение последней заполненной ячейки в столбце с помощью функции INDEX(): = INDEX(A:A;ACCOUNT(A:A))

Обращение к целым столбцам и строкам требует значительных ресурсов и может замедлить преобразование электронной таблицы. Если вы уверены, что пользователь не выйдет за пределы определенного диапазона при вводе значений, лучше ссылаться на диапазон, а не на столбец. В этом случае формула будет выглядеть следующим образом: =INDEX(A1:A20;ACCOUNT(A1:A20))

Читайте так же:
Как быстро вставить имя пользователя Windows в ячейку в Excel?

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

Чтобы определить номер строки последней заполненной ячейки в списке, которая находится в диапазоне E8:E30 (т.е. не начинается с первой строки), используется формула: =COUNT(E9:E30)+LINE(E8).

В функции STRIKE() возвращается номер строки заголовка списка. Значение из последней заполненной ячейки списка выводится с помощью функции ИНДЕКС() : = ИНДЕКС(E9:E30;ACCOUNT(E9:E30))

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

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

При наличии диапазона числовых значений можно использовать =POPES(1E+306;A:A;1), чтобы найти номер строки для последней заполненной ячейки. Значения пустых ячеек и текстов игнорируются.

В случае массивов, у которых определен целый столбец ( A:A ), функция ПОИСКПОЗ() возвращает последнюю заполненную строку. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, меньшего или равного 1E+306. Однако это требует сортировки массива по возрастанию. Эта функция возвращает последнюю заполненную строку, если столбец не отсортирован, что нам и нужно.

Вы можете использовать формулу для возврата значения последней заполненной ячейки в списке в диапазоне A2:A20: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))

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

Если необходимо определить номер строки последнего текстового значения (также при наличии пробелов), формулу необходимо переделать: =DESCRIPTION(«*»;$A:$A;-1)

Пустые ячейки, числа и текстовые значения (такие, как «») игнорируются.

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

Если столбец содержит как числовые, так и текстовые значения, можно предложить универсальное решение для определения номера строки последней заполненной ячейки: =MAX(SEARCH(«*»;$A:$A;-1);0); SEARCH(SEARCH(1E+306;$A:$A;1);0)

Функция ERROR() используется для подавления ошибки, возникающей, когда столбец А содержит только текст или только числовые значения.

Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>«»))

Читайте так же:
Как вставить гистограмму в ячейку в Excel?

После ввода формулы массива нажмите CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазоне A1:A20. Лучше всего задать фиксированный диапазон поиска, так как использование ссылок на целые строки или столбцы в формулах массива требует значительных ресурсов.

Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>«»)))

Как обычно, после ввода формулы таблицы нажмите CTRL + SHIFT + ENTER, а не ENTER .

СОВЕТ: Как вы можете видеть, это очень затрудняет подсчет, когда в диапазоне есть пробелы. Поэтому при заполнении и проектировании таблиц хорошо следовать принципам, изложенным в статье Советы по построению таблиц.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector