Excel vba поиск в умной таблице

Обновлено: 08.01.2025

Очень часто при внесении данных на лист 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 - это номер строки, последнюю заполненную ячейку в которой мы определяем.

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

Способ 2:
Определение последней заполненной строки через 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 не считает её пустой(к слову совершенно справедливо) и включает в просмотр при поиске последней ячейки.

Метод Find объекта Range предназначен для поиска ячейки и сведений о ней в заданном диапазоне по ее значению, формуле и примечанию. Чаще всего этот метод используется для поиска в таблице ячейки по слову, части слова или фразе, входящей в ее значение.

Синтаксис метода Range.Find

Expression . Find ( What , After , LookIn , LookAt , SearchOrder , SearchDirection , MatchCase , MatchByte , SearchFormat )

Expression – это переменная или выражение, возвращающее объект Range, в котором будет осуществляться поиск.

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

Метод Range.Find возвращает объект Range, представляющий из себя первую ячейку, в которой найдена поисковая фраза (параметр What). Если совпадение не найдено, возвращается значение Nothing.

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

Параметры метода Range.Find

xlValues (-4163) – значения;
xlComments (-4144) – примечания*;
xlNotes (-4144) – примечания*;
[xlFormulas (-4123) – формулы]**.

xlWhole (1) – полное совпадение;
xlPart (2) – частичное совпадение.

xlByRows (1) – поиск по строкам;
xlByColumns (2) – поиск по столбцам.

xlNext (1) – поиск вперед;
xlPrevious (2) – поиск назад.

False (0) – поиск без учета регистра (по умолчанию);
True (1) – поиск с учетом регистра.

False (0) – двухбайтовый символ может соответствовать однобайтовому символу;
True (1) – двухбайтовый символ должен соответствовать только двухбайтовому символу.

* Примечания имеют две константы с одним значением. Проверяется очень просто: MsgBox xlComments и MsgBox xlNotes .
** Тесты показали неработоспособность метода Range.Find с константой xlFormulas в моей версии VBA Excel.

В справке Microsoft тип данных всех параметров, кроме SearchDirection, указан как Variant.

Хотите создавать решения, которые расширяют возможности Office на разнообразных платформах? Ознакомьтесь с новой моделью надстроек Office. Надстройки Office занимают меньше места по сравнению с надстройками и решениями VSTO, и вы можете создавать их, используя практически любую технологию веб-программирования, например HTML5, JavaScript, CSS3 и XML.

Синтаксис

выражение.Find (What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

выражение: переменная, представляющая объект Range.

Параметры

Имя	Обязательный или необязательный	Тип данных	Описание
What	Обязательный	Variant	Искомые данные. Может быть строкой или любым типом данных Microsoft Excel.
After	Необязательный	Variant	Ячейка, после которой нужно начать поиск. Соответствует положению активной ячейки, когда поиск выполняется из пользовательского интерфейса.

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

Возвращаемое значение

Объект Range, представляющий первую ячейку, в которой обнаружены требуемые сведения.

Примечания

Этот метод возвращает значение Nothing, если совпадения не найдены. Метод Find не влияет на выделенный фрагмент или активную ячейку.

Параметры для аргументов LookIn, LookAt, SearchOrder и MatchByte сохраняются при каждом использовании этого метода. Если вы не укажете значения этих аргументов при следующем вызове метода, будут использоваться сохраненные значения. Установка этих аргументов изменяет параметры в диалоговом окне Найти, а изменение параметров в диалоговом окне Найти приводит к изменению сохраненных значений, которые используются, если опустить аргументы. Чтобы избежать проблем, устанавливайте эти аргументы явным образом при каждом использовании этого метода.

Для повторения поиска можно использовать методы FindNext и FindPrevious.

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

Чтобы найти ячейки, отвечающие более сложным шаблонам, используйте инструкцию For Each. Next с оператором Like. Например, следующий код выполняет поиск всех ячеек в диапазоне A1:C5, где используется шрифт, имя которого начинается с букв Cour. Когда Microsoft Excel находит соответствующее значение, шрифт изменяется на Times New Roman.

Примеры

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие значение 2, и изменить значение ячейки на 5. Таким образом, значения 1234 и 99299 содержали 2 и оба значения ячеек станут 5.

В этом примере показано, как найти все ячейки в диапазоне A1:A500 на листе 1, содержащие подстроку "abc", и изменить ее на "xyz".

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

Кто не знает метод FIND в Excel? Я уверен, что все знают, кто имеет дело с рабочими листами Excel. НАЙТИ или популярную комбинацию клавиш Ctrl + F найдет слово или контент, который вы ищете во всей рабочей таблице, а также во всей рабочей книге. Когда вы говорите, что найти означает, что вы находите в ячейках или диапазонах не так ли? Да, правильный метод поиска является частью ячеек или диапазонов в Excel, а также в VBA.

Аналогично, в VBA Find у нас есть опция под названием FIND function, которая может помочь нам найти искомое значение. В этой статье я познакомлю вас с методологией FIND в VBA.

Формула для поиска функции в Excel VBA

В обычной рабочей таблице Excel мы просто нажимаем сочетание клавиш Ctrl + F, чтобы найти содержимое. Но в VBA нам нужно написать функцию, чтобы найти контент, который мы ищем. Хорошо, тогда давайте посмотрим на синтаксис FIND.

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

Если вы наблюдаете за тем, что есть в обычных Ctrl + F, все также присутствует в синтаксисе VBA Find. Теперь посмотрим, о чем говорит каждое слово в синтаксисе.

Что: просто то, что вы ищете. Здесь нужно упомянуть контент, который мы ищем.

После: после какой ячейки вы хотите искать.

LookIn: где искать то, что вы ищете. Например, формулы, значения или комментарии. Параметры: xlFormulas, xlValues, xlComments.

LookAt: ищите ли вы весь контент или только часть контента. Параметры: xlWhole, xlPart.

SearchOrder: вы ищете в строках или столбцах. xlByRows или xlByColumns.

SearchDirection: вы смотрите на следующую или предыдущую ячейку. xlNext, xlPrevious.

MatchCase: содержимое, которое вы ищете, чувствительно к регистру или нет. Правда или ложь.

MatchByte: это только для двухбайтовых языков. Правда или ложь.

SearchFormat: вы ищете форматирование. Если вы ищете формат, вам нужно использовать метод Application.FindFormat .

Это объяснение синтаксиса метода VBA FIND. Помимо первого параметра все необязательно. В разделе примеров мы увидим, как использовать этот метод FIND в кодировании VBA.

Как использовать функцию поиска VBA Excel?

Мы научимся использовать функцию VBA Find Excel на нескольких примерах.

Вы можете скачать этот шаблон VBA Find Excel здесь - VBA найти шаблон Excel

Функция поиска VBA - пример № 1

Прежде всего позвольте мне объяснить вам простой пример использования свойства FIND и найти контент, который мы ищем. Предположим, ниже приведены данные, которые вы имеете в своем листе Excel.

Шаг 1: Из этого я хочу найти имя Джон, давайте откроем Visual Basic и начнем кодирование.

Код:

Шаг 2: Здесь вы не можете начать слово FIND, потому что FIND является частью свойства RANGE. Итак, во-первых, нам нужно упомянуть, где мы смотрим, т.е. Range.

Шаг 3: Итак, сначала укажите диапазон, в котором мы ищем. В нашем примере наш диапазон от B2 до B11.

Код:

Шаг 4: После упоминания диапазона поставьте точку (.) И введите FIND. Вы должны увидеть свойство НАЙТИ.

Шаг 5: Выберите свойство НАЙТИ и откройте скобку.

Шаг 6: Наш первый аргумент - это то, что мы ищем. Чтобы выделить аргумент, мы можем передать аргумент наподобие What: =, это будет полезно для определения того, на какой параметр мы ссылаемся.

Код:

Шаг 7: Последняя часть после нахождения слова, что мы хотим сделать. Нам нужно выбрать слово, поэтому передайте аргумент как. Выберите

Код:

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

Функция поиска VBA - пример № 2

Теперь я покажу вам, как найти слово комментария, используя метод find. У меня есть данные, и в трех ячейках у меня есть комментарий.

В ячейках с красным флажком есть комментарии. Из этого комментария я хочу найти слово «Без комиссии».

Шаг 1: начните код с упоминания диапазона («D2: D11») и поставьте точку (.) И введите Find

Код:

Шаг 2: В аргументе WHAT введите слово «Без комиссии».

Код:

Шаг 3: Пропустите часть After и выберите часть LookIn. В части LookIn мы ищем это слово в комментариях, поэтому выберите xlComments, а затем передайте аргумент как .Select

Код:

Шаг 4: Теперь запустите этот код, используя клавишу F5 или вручную, как показано на рисунке, чтобы он выбрал ячейку с комментарием «Без комиссии». В ячейке D9 есть упомянутый комментарий.

Если искомое слово не будет найдено в диапазоне, мы предоставили код VBA, который будет возвращать ошибку, подобную этой.

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

То, что нужно запомнить

VBA FIND является частью свойства RANGE, и вам нужно использовать FIND только после выбора диапазона.
В FIND первый параметр является обязательным (What), кроме этого все остальное является необязательным.
Если вам нужно найти значение после определенной ячейки, вы можете упомянуть ячейку в параметре After синтаксиса Find.