Поиск последней ячейки — все о данных
Поиск последней использованной строки, столбца или ячейки — одна из наиболее распространенных задач при написании макросов и приложений VBA. Как и все в Excel и VBA, есть много разных способов.
Выбор правильного метода в основном зависит от того, как выглядят ваши данные.
В этой статье я объясню три различных метода VBA объекта Range, которые мы можем использовать для поиска последней ячейки на листе. У каждого из этих методов есть свои плюсы и минусы, и некоторые из них страшнее других.
Но понимание того, как работает каждый метод, поможет вам узнать, когда их использовать и почему.
# 1 — Метод Range.End
Range.End () очень похож на сочетание клавиш Ctrl + Arrow. В VBA вы можете использовать этот метод, чтобы найти последнюю непустую ячейку в той же строке или столбце.
Диапазон: конец примера кода VBA
Sub Last_Cell () ‘Найти последнюю непустую ячейку в той же строке или столбце Dim lRow As Long Dim lCol As Long’ Найти последнюю непустую ячейку в столбце A (1) lRow = Cells (Rows.Count, 1) . End (xlUp) .Row ‘Найти последнюю непустую ячейку в строке 1 lCol = Cells (1, Columns.Count) .End (xlToLeft) .Column MsgBox «Последняя строка:» & lRow & vbNewLine & _ «Последний столбец : «& lCol Fine Sub
Чтобы найти последнюю использованную строку в столбце, этот метод начинается с последней ячейки в столбце и идет вверх (xlUp), пока не найдет первую непустую ячейку.
Оператор Rows.Count возвращает количество всех строк на листе. Поэтому на практике мы указываем последнюю ячейку в столбце A листа (ячейка A1048567) и идем вверх, пока не найдем первую непустую ячейку.
То же самое и с поиском в последнем столбце. Начните с последнего столбца строки, затем двигайтесь влево, пока не будет найдена последняя непустая ячейка в столбце. Columns.Count возвращает общее количество столбцов на листе. Итак, начнем с последнего столбца и будем двигаться влево.
Аргумент метода End указывает, в каком направлении двигаться. Возможные варианты: xlDown, xlUp, xlToLeft, xlToRight.
Pro of Range.End
- Range.End прост в использовании и понимании, поскольку он работает так же, как сочетания клавиш Ctrl + Arrow.
- Его можно использовать для поиска первой пустой ячейки или последней непустой ячейки в той же строке или столбце.
Минусы Range.End
- Range.End работает только с одной строкой или столбцом. Если у вас есть диапазон данных, содержащий пробелы в последней строке или столбце, может быть сложно определить, в какой строке или столбце запустить метод.
- Если вы хотите найти последнюю использованную ячейку, вам нужно оценить как минимум два оператора. Один для поиска последней строки и один для поиска последнего столбца. Затем их можно объединить для ссылки на последнюю ячейку.
Вот справочные статьи для Range.End
- Страница справки MSDN для Range.End
- Руководство MSDN для перечислений xlDirection
# 2 — Метод Range.Find
Метод Range.Find: я предпочитаю этот метод для поиска последней строки, столбца или ячейки. Он самый универсальный, но и самый устрашающий.
У Range.Find много аргументов, но пусть это вас не пугает. Как только вы узнаете, что они делают, вы можете использовать Range.Find для многих вещей в VBA.
Range.Find — это в основном способ программирования меню поиска в Excel. Он делает то же самое, и большинство аргументов Range.Find — это пункты меню «Найти.
Гамма — найти образец кода
Ниже приведен код для поиска последней непустой строки.
Sub Last_Cell_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) и прокручивает каждую строку, пока не найдет непустую ячейку. Если обнаружено непустое значение, он останавливается и возвращает номер строки.
Вот подробное объяснение каждой темы.
- Что: = ”*” — звездочка — это подстановочный знак, который соответствует любому тексту или числу в ячейке. По сути, это то же самое, что найти непустую ячейку.
- After: = Range («A1») — запускает поиск после ячейки A1, первой ячейки листа. Это означает, что 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, это помогает. Этот аргумент не требуется для этого сценария.
Да, я знаю, что это много, но я надеюсь, что вы лучше понимаете, как использовать эти аргументы, чтобы найти что-то на листе.
Pro of Range.Find
- Поиск выполняет поиск во всем диапазоне последней непустой строки или столбца. Он НЕ ограничен одной строкой или столбцом.
- Последняя строка в наборе данных может содержать пробелы, и Range.Find все равно найдет последнюю строку.
- Аргументы можно использовать для поиска в разных направлениях и определенных значений, а не только пустых ячеек.
Минусы Range.Find
- это ужасно. Метод содержит 9 аргументов. Хотя требуется только один из этих аргументов (What), вы должны привыкнуть использовать по крайней мере первые 7 аргументов. В противном случае метод Range.Find по умолчанию будет использовать последние использованные настройки в поле поиска. Это важно. Если вы не предоставите необязательные аргументы для LookAt, LookIn и SearchOrder, метод Find будет использовать последние использованные параметры в поле поиска Excel.
- Для поиска последней ячейки требуются две инструкции. Один для поиска последней строки и один для поиска последнего столбца. Поэтому вам нужно объединить их, чтобы найти последнюю ячейку.
Macro Recorder поможет!
Range.Find по-прежнему остается моим любимым методом поиска последней ячейки из-за его универсальности. Но есть что написать и вспомнить. К счастью, вам это не нужно.
вы можете использовать средство записи макросов, чтобы быстро сгенерировать код со всеми аргументами.
- Запустить средство записи макросов
- Нажмите Ctrl + F
- Затем нажмите кнопку «Найти далее»
Код для метода Find со всеми аргументами будет сгенерирован регистратором макросов.
Используйте настраиваемую функцию для метода поиска
Вы также можете использовать пользовательскую функцию (UDF) для метода поиска. Последняя особенность Рона де Брюэна — прекрасный тому пример. Вы можете скопировать эту функцию в любой проект или модуль кода VBA и использовать ее для возврата последней строки, столбца или ячейки.
# 3 — Range.SpecialCells (xlCellTypeLastCell)
Метод SpecialCells делает то же самое, что и нажатие сочетания клавиш Ctrl + End, и выбирает последнюю использованную ячейку на листе.
Пример кода SpecialCells (xlCellTypeLastCell)
Sub Range_SpecialCells_Method () Диапазон MsgBox («A1»). SpecialCells (xlCellTypeLastCell) .Address End Sub
На самом деле это самый простой способ найти последнюю использованную ячейку. Однако этот метод находит последнюю использованную ячейку, которая может отличаться от последней непустой ячейки.
Часто нажимайте Ctrl + End на клавиатуре, и вы попадете в ячейку в нижней части листа, которая определенно не используется. Это может произойти по разным причинам. Распространенной причиной является изменение свойств форматирования этой ячейки. Простое изменение размера шрифта или цвета заливки ячейки помечает ее как используемую ячейку.
Профи диапазона
- Вы можете использовать этот метод, чтобы найти «используемые» строки и столбцы в конце листа и удалить их. Сравнение результата Range.SpecialCells с результатом Range.Find для непробелов может помочь вам быстро определить, есть ли на вашем листе неиспользуемые строки или столбцы.
- Удаление неиспользуемых строк / столбцов может уменьшить размер файла и увеличить полосу прокрутки.
Минусы Range.SpecialCells
- Excel сбрасывает только последнюю ячейку при сохранении книги. Следовательно, если пользователь или макрос удаляет содержимое некоторых ячеек, этот метод не найдет последнюю истинную ячейку, пока файл не будет сохранен.
- Найдите последнюю использованную ячейку, а НЕ последнюю непустую ячейку.
Другие способы найти последнюю ячейку
Что ж, это должно охватывать основы поиска последней использованной или непустой ячейки на листе. Если лист содержит объекты (таблицы, диаграммы, сводные таблицы, срезы и т.д.), Вам может потребоваться использовать другие методы для поиска последней ячейки. Я объясню эти методы в отдельном посте.
Оставьте комментарий ниже, если у вас есть какие-либо вопросы или вы все еще не можете найти последнюю ячейку, я буду рад помочь!