Поиск первой пустой ячейки в строке vba excel
Обновлено: 04.07.2024
Что делает макрос: Вы часто можете столкнуться с необходимостью добавить строки или столбцы к существующему набору данных. Этот макрос позволяет автоматически найти и выбрать первую пустую строку или столбец. Он предназначен для использования в сочетании с другими макрокомандами.
Как макрос работает
Эти макросы используют элемент Cells и свойство Offset в качестве ключевых инструментов навигации.
Элемент Cells принадлежит объекту Range. Это дает нам чрезвычайно удобный способ выбора диапазонов с помощью кода. Для этого требуется только относительные строки и столбцы в качестве параметров. Cells(5,4) приводит к строке 5, столбцу 4 (или ячейке D5). Cells(16, 4) приводит к строке 16, столбцу 4 (или ячейке D16).
Помимо передачи жестких чисел в элемент Cells, вы также можете передавать выражения.
Cells(Rows.Count, 1) то же самое, что выбрать последнюю строку и первый столбец в таблице. В Excel 2010 переводит к ячейке A1048576.
Cells(1,Columns.Count) выбирает первую строку и последний столбец в таблице. В Excel 2010 приводит к ячейке XFD1.
Объединение оператора Cells со свойством End позволяет перейти к последней использованной строке или столбцу. Это утверждение эквивалентно переходит к ячейке A1048576 и нажав Ctrl + Shift + стрелка вверх на клавиатуре. Excel автоматически переходит к последней использованной строки в столбце A.
Переходит к ячейке XFD1 и нажав Ctrl + Shift + стрелка влево на клавиатуре. Это переводит вас к последней использованной колонке в строке 1.
Когда вы дойдете до последней строки или столбца, вы сможете использовать свойство Offset для перемещения вниз или к следующей пустой строке или столбцу.
Свойство Offset использует индекс строки и столбца, чтобы указать изменяющуюся базовую точку.Например, оператор выбирает ячейку А2, так как индекс строки в Offset перемещения строки базовой точки на единицу:
Этот оператор выбирает ячейку С4, и перемещает базовую точку на три строки и два столбца:
Используя все эти понятия вместе, мы можем создать макрос, который выбирает первую пустую строку или столбец.
Возвращаемая свойством Range.End ячейка в зависимости от расположения и содержания исходной:
| Исходная ячейка | Возвращаемая ячейка |
|---|---|
| Исходная ячейка пустая | Первая заполненная ячейка или, если в указанном направлении заполненных ячеек нет, последняя ячейка строки или столбца в заданном направлении. |
| Исходная ячейка не пустая и не крайняя внутри исходного заполненного диапазона в указанном направлении | Последняя заполненная ячейка исходного заполненного диапазона в указанном направлении |
| Исходная ячейка не пустая, но в указанном направлении является крайней внутри исходного заполненного диапазона | Первая заполненная ячейка следующего заполненного диапазона или, если в указанном направлении заполненных ячеек нет, последняя ячейка строки или столбца в заданном направлении. |
Синтаксис
Expression — выражение (переменная), представляющее объект Range.
Параметры
| Параметр | Описание |
|---|---|
| Direction | Константа из коллекции XlDirection, задающая направление перемещения. Обязательный параметр. |
| Константа | Значение | Направление |
|---|---|---|
| xlDown | -4121 | Вниз |
| xlToLeft | -4159 | Влево |
| xlToRight | -4161 | Вправо |
| xlUp | -4162 | Вверх |
Примеры
Скриншот области рабочего листа для визуализации примеров применения свойства Range.End:
Примеры возвращаемых ячеек свойством End объекта Range(«C10») с разными значениями параметра Direction:
Список данных может быть заполнен пустыми ячейками. Чтобы найти первую пустую ячейку в выбранном диапазоне столбца, просмотрите приведенное ниже руководство для получения дополнительных сведений.
Найдите первую пустую ячейку в столбце с формулой
В этом разделе мы покажем вам, как найти первую пустую ячейку в столбце с формулой. В качестве примера возьмите приведенный ниже список данных, пожалуйста, сделайте следующее.
1. Выберите пустую ячейку для отображения результата.
2. Скопируйте и вставьте формулу. = МИН (ЕСЛИ (A1: A23 = ""; СТРОКА (A1: A23))) в панель формул, затем нажмите Ctrl + Shift + Enter клавиши на клавиатуре. Смотрите скриншот:
Внимание: Измените диапазон в формуле на свой собственный.
После этого в выбранной ячейке отобразится номер строки. Поскольку указанный вами диапазон находится в столбце A, в зависимости от отображаемого числа это означает, что первая пустая ячейка - это A16.
Найдите первую пустую ячейку в столбце с кодом VBA
Помимо формулы, вы также можете найти первую пустую ячейку в столбце с кодом VBA. Пожалуйста, сделайте следующее.
1. Нажмите другой + A11 в то же время открыть Microsoft Visual Basic для приложений окно.
2. в Microsoft Visual Basic для приложений окна, дважды щелкните текущее имя рабочего листа на левой панели, чтобы открыть редактор кода.
3. Скопируйте и вставьте следующий код VBA в редактор.
Код VBA: найти первую пустую ячейку в столбце
4. Нажмите Запустите кнопку или нажмите кнопку F5 ключ для запуска кода.
5. После запуска кода KutoolsforExcel появится диалоговое окно, выберите диапазон, в котором вы хотите найти первую пустую ячейку, затем щелкните OK кнопка. Смотрите скриншот:
Затем появляется диалоговое окно с адресом первой пустой ячейки в указанном столбце, указанном внутри.
Внимание: Если в выбранном диапазоне есть другие пустые ячейки, указанное выше диалоговое окно будет появляться до тех пор, пока не будут найдены все пустые ячейки в диапазоне.
Поиск какого-либо значения в ячейках Excel довольно часто встречающаяся задача при программировании какого-либо макроса. Решить ее можно разными способами. Однако, в разных ситуациях использование того или иного способа может быть не оправданным. В данной статье я рассмотрю 2 наиболее распространенных способа.
Поиск перебором значений
Довольно простой в реализации способ. Например, найти в колонке "A" ячейку, содержащую "123" можно примерно так:
Минусами этого так сказать "классического" способа являются: медленная работа и громоздкость. А плюсом является его гибкость, т.к. таким способом можно реализовать сколь угодно сложные варианты поиска с различными вычислениями и т.п.
Поиск функцией Find
Гораздо быстрее обычного перебора и при этом довольно гибкий. В простейшем случае, чтобы найти в колонке A ячейку, содержащую "123" достаточно такого кода:
Вкратце опишу что делают строчки данного кода:
1-я строка: Выбираем в книге лист "Данные";
2-я строка: Осуществляем поиск значения "123" в колонке "A", результат поиска будет в fcell;
3-я строка: Если удалось найти значение, то fcell будет содержать Range-объект, в противном случае - будет пустой, т.е. Nothing.
Полностью синтаксис оператора поиска выглядит так:
Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
What - Строка с текстом, который ищем или любой другой тип данных Excel
After - Ячейка, после которой начать поиск. Обратите внимание, что это должна быть именно единичная ячейка, а не диапазон. Поиск начинается после этой ячейки, а не с нее. Поиск в этой ячейке произойдет только когда весь диапазон будет просмотрен и поиск начнется с начала диапазона и до этой ячейки включительно.
LookIn - Тип искомых данных. Может принимать одно из значений: xlFormulas (формулы), xlValues (значения), или xlNotes (примечания).
LookAt - Одно из значений: xlWhole (полное совпадение) или xlPart (частичное совпадение).
SearchOrder - Одно из значений: xlByRows (просматривать по строкам) или xlByColumns (просматривать по столбцам)
SearchDirection - Одно из значений: xlNext (поиск вперед) или xlPrevious (поиск назад)
MatchCase - Одно из значений: True (поиск чувствительный к регистру) или False (поиск без учета регистра)
MatchByte - Применяется при использовании мультибайтных кодировок: True (найденный мультибайтный символ должен соответствовать только мультибайтному символу) или False (найденный мультибайтный символ может соответствовать однобайтному символу)
SearchFormat - Используется вместе с FindFormat. Сначала задается значение FindFormat (например, для поиска ячеек с курсивным шрифтом так: Application.FindFormat.Font.Italic = True), а потом при использовании метода Find указываем параметр SearchFormat = True. Если при поиске не нужно учитывать формат ячеек, то нужно указать SearchFormat = False.
Чтобы продолжить поиск, можно использовать FindNext (искать "далее") или FindPrevious (искать "назад").
Примеры поиска функцией Find
Пример 1: Найти в диапазоне "A1:A50" все ячейки с текстом "asd" и поменять их все на "qwe"
Обратите внимание : Когда поиск достигнет конца диапазона, функция продолжит искать с начала диапазона. Таким образом, если значение найденной ячейки не менять, то приведенный выше пример зациклится в бесконечном цикле. Поэтому, чтобы этого избежать (зацикливания), можно сделать следующим образом:
Пример 2: Правильный поиск значения с использованием FindNext, не приводящий к зацикливанию.
В ниже следующем примере используется другой вариант продолжения поиска - с помощью той же функции Find с параметром After. Когда найдена очередная ячейка, следующий поиск будет осуществляться уже после нее. Однако, как и с FindNext, когда будет достигнут конец диапазона, Find продолжит поиск с его начала, поэтому, чтобы не произошло зацикливания, необходимо проверять совпадение с первым результатом поиска.
Пример 3: Продолжение поиска с использованием Find с параметром After.
Следующий пример демонстрирует применение SearchFormat для поиска по формату ячейки. Для указания формата необходимо задать свойство FindFormat.
Пример 4: Найти все ячейки с шрифтом "курсив" и поменять их формат на обычный (не "курсив")
Хочу обратить внимание на то, что в этом примере я не стал использовать "защиту от зацикливания", как в Примерах 2 и 3, т.к. шрифт меняется и после "прохождения" по всем ячейкам, больше не останется ни одной ячейки с курсивом.
Свойство FindFormat можно задавать разными способами, например, так:
Поиск последней заполненной ячейки с помощью Find
Следующий пример - применение функции Find для поиска последней ячейки с заполненными данными. Использованные в Примере 4 SpecialCells находит последнюю ячейку даже если она не содержит ничего, но отформатирована или в ней раньше были данные, но были удалены.
Пример 5: Найти последнюю колонку и столбец, заполненные данными
Для поиска функцией Find по маске (шаблону) можно применять символы:
* - для обозначения любого количества любых символов;
? - для обозначения одного любого символа;
- для обозначения символов *, ? и
. (т.е. чтобы искать в тексте вопросительный знак, нужно написать
?, чтобы искать именно звездочку (*), нужно написать
* и наконец, чтобы найти в тексте тильду, необходимо написать
Поиск в скрытых строках и столбцах
Для поиска в скрытых ячейках нужно учитывать лишь один нюанс: поиск нужно осуществлять в формулах, а не в значениях, т.е. нужно использовать LookIn:=xlFormulas
Поиск даты с помощью Find
Если необходимо найти текущую дату или какую-то другую дату на листе Excel или в диапазоне с помощью Find, необходимо учитывать несколько нюансов:
Приведу несколько примеров поиска даты.
Пример 7: Найти текущую дату на листе независимо от формата отображения даты.
Пример 8: Найти 1 марта 2018 г.
Искать часть даты - сложнее. Например, чтобы найти все ячейки, где месяц "март", недостаточно искать "03" или "3". Не работает с датами так же и поиск по шаблону. Единственный вариант, который я нашел - это выбрать формат в котором месяц прописью для ячеек с датами и искать слово "март" в xlValues.
Тем не менее, можно найти, например, 1 марта независимо от года.
Пример 9: Найти 1 марта любого года.
Книги по теме:
Посмотреть все книги по программированию
Комментарии к статье:
| 10.09.17 Дмитрий | Очень толковая и полезная статья. Помогла мне существенно ускорить мой код. Спасибо! |
| 23.11.17 Гость | Спасибо, хорошая статья. |
| 03.12.17 Владимир | Спасибо! Использую в своих проектах. |
| 07.12.17 Эд | Спасибо, очень пригодилась Ваша статья! |
| 19.01.18 Николай | .find не ищет значение в ячейке, если ячейка в скрытой строке. .match позволяет не беспокоится о том, что на листах с источниками данных строка с искомым значением будет скрыта из-за установленного фильтра. |
| 05.02.18 Владимир | Большое спасибо! Очень толково и понятно. |
| 11.03.18 Гость | Здравствуйте, А если мне требуется найти ячейку в определенном столбце с определенным значением, например "строка 1" и если я нахожу такую, то через несколько строк(неизвестно сколько) мне нужно раскрасить следующую ячейку в другом столбце(самую ближайшую) и остановить цикл, и опять по новой искать дальше "строка 1". Можете посоветовать? |
| 26.03.18 Гость | Спасибо! Все бы так описывали! Все доступно и понятно)) |
| 23.05.18 Аркадий | В VBA я новичок. Активно использую интернет для своих вопросов, однако таких информативных, лаконичных и простых в понимании сайтов не много. Огромное спасибо автору! Адрес уже в закладках. |
| 21.07.18 Гость | Спасибо! Уже несколько недель искала подобное! |
| 25.07.18 Joann | Метод .find (в случае далнейших множественных обращений к этому механизму) разумно вынести в отдельную функцию, как из управляющей процедуры передать в эту функцию ее параметры (обязательный текстовый параметр 'what:=' - передается без проблем, а вот значения ('xlValues', 'xlWhole', . ) для 'LookIn:=', 'LookAt:=', . - приводят к ошибке)? |
| 15.08.18 Марат | Спасибо за статью, пополнил свои знания в части метода Find. Простые переборы хороши на небольших диапазонах. А когда нужно обработать сотни тысяч ячеек, Find - хороший инструмент! |
| 28.08.18 Гость | Познавательно и подробно. Спасибо за статью. |
| 16.10.18 Гость | Хорошая статья. Спасибо |
| 29.10.18 Sega | Полезная статейка. Спасибо. |
| 14.11.18 Гость | Статья для начинающих, а тонкости поиска по дате нет ни одного примера |
| 02.02.19 Ибрагим | Чушь полная, плагиат! |
| 01.03.19 inexsu.wordpress.com | Loop While Not c Is Nothing And c.Address <> firstResult Когда c станет Nothing, c.Address даст ошибку Решение https://inexsu.wordpress.com/2018/03/05/range-findnext-method/ |
| 01.03.19 Администратор | Вы правы, такое действительно может произойти, например, если менять значения найденных ячеек. Т.е., например, ищем значения "asd" и меняем их на "qwe". При замене последнего значения, FindNext ничего уже не найдет, вернет Nothing и произойдет ошибка в условии. Внес изменения в статье. Спасибо вам за подсказку. |
| 07.04.19 Гость | Добрый день. есть 2 таблица на разных листах. макрос нашел нужные ячейки с ИНН по 1 условию, затем мне надо чтобы макрос нашел в таблице 2 на листе 2 все названия клиентов с инн по условию 1 из таблицы 1 с листа 1. как тогда использовать find. |
| 07.04.19 Гость | Лучше использовать функцию ВПР |
| 21.05.19 Гость | Очень подробная статья. Отдельное спасибо за множество примеров. |
| 17.10.19 Михаил | Спасибо большое все понятно доступно и очень полезно |
| 31.10.19 Гость | Спасибо. |
| 01.11.19 Гость | ку |
| 01.11.19 Гость | 2 раза |
| 19.11.19 Kamol | Отлично. Поиск с двумя значениями Пример в колонке А="123" и В="456" |
| 25.12.19 Гость | Спасибо! |
| 05.03.20 Гость | Добрый день! Спасибо за код автору Вопрос |
Как сделать поиск слова по, но с любым регистром и любым набором символов
Пример: Я ввожу слово через Textbox
В таблице есть слово компьютер, колодец, ком
Нужно, чтобы программа находила по запросу "омпьюте", "пьюте" слово компьютер а по запросу "ком" слова компьютер и ком"
IsEmpty - это функция, которая используется для проверки, является ли ячейка, на которую ссылаются, пустой или нет. Это очень похоже на функцию ISBLANK в Excel. Функция IsEmpty в Excel VBA также называется информационной функцией в Excel, так как она дает информацию о том, является ли данная ячейка пустой или нет.
Синтаксис IsEmpty в Excel VBA
Синтаксис для функции Isempty следующий:
Мы можем использовать эту функцию, чтобы проверить, является ли одна ячейка пустой или весь диапазон данных пуст или нет. Эта функция возвращает два значения. Одно верно, а другое ложно. Если указанная ячейка пуста, функция возвращает значение как true, а если указанная ячейка не пуста, она возвращает значение как false.
Как использовать функцию VBA IsEmpty в Excel?
Мы узнаем, как использовать функцию VBA IsEmpty с несколькими примерами в Excel.
Вы можете скачать этот шаблон VBA IsEmpty Excel здесь - Шаблон VBA IsEmpty Excel
VBA IsEmpty - Пример № 1
Во-первых, давайте посмотрим значение, возвращаемое этой функцией, как она работает.
Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:
Шаг 1: Перейдите на вкладку разработчика и нажмите на Visual Basic.
Шаг 2: Напишите следующий код в проекте.
Код:
Во-первых, давайте поймем код, написанный выше, шаг за шагом:
- Проверка - это имя определенной подфункции.
- Mycheck - это переменная, которую мы определили как строку, потому что функция Isempty возвращает логическое значение.
- Mycheck сохраняет значение Isempty, возвращаемое при проверке ячейки A1.
- Значение, хранящееся в Mycheck Variable, отображается функцией MsgBox.
Шаг 3: Запустите код, нажав кнопку запуска.
Мы можем видеть, что возвращаемое функцией значение равно true, поскольку ячейка A1 пуста.
VBA IsEmpty - Пример № 2
Теперь давайте воспользуемся функцией Isempty с функцией if для проверки определенной ячейки на рабочем листе, является ли она пустой или нет.
Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:
Шаг 1: На вкладке разработчика нажмите Visual Basic под разделом кода.
Шаг 2: Напишите следующий код в окне кода,
Код:
Давайте еще раз поймем код, написанный выше, еще раз.
Шаг 3: Запустите приведенный выше код, нажав на кнопку «Выполнить».
Мы видим, что результат отображается как ячейка A1 пуста.
Шаг 4: Теперь поместите случайное значение в ячейку A, например, я поместил значение A в ячейку A1.
Шаг 5: Теперь снова запустите код, и мы получим следующий результат.
VBA IsEmpty - Пример № 3
Теперь давайте используем эту функцию, чтобы выяснить, является ли данный диапазон ячеек пустым или нет. Ранее мы использовали эту функцию в одной ячейке. В этом примере наш диапазон данных будет от B1: D7.
Чтобы использовать функцию VBA IsEmpty в Excel, выполните следующие действия:
Шаг 1: На вкладке разработчика нажмите Visual Basic под разделом кода.
Шаг 2: Напишите следующий код в окне кода,
Код:
Давайте разберемся в написанном выше коде шаг за шагом.
Шаг 3: Запустите приведенный выше код, нажав на кнопку «Выполнить».
Мы видим следующий результат, поскольку ячейка A1 пуста.
То, что нужно запомнить
Есть несколько вещей, которые мы должны помнить об Isempty Function в Excel VBA:
- Isempty похож на функцию Isblank в Excel.
- IsEmpty - это информационная функция.
- Функция IsEmpty возвращает логическое значение, т.е. true или false.
- Функция IsEmpty может использоваться для одной ячейки или для диапазона ячеек.
Рекомендуемые статьи
Это было руководство для VBA IsEmpty. Здесь мы обсудили, как использовать функцию Excel VBA IsEmpty вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Читайте также: