Vba excel проверка диапазона на пустоту

Обновлено: 07.01.2025

Можно ли написать макрос vba, который определяет, есть ли какие-либо пустые ячейки в данном диапазоне и возвращает номер строки этой ячейки?

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

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

Небольшая справочная информация: у меня есть очень большой файл (около 80 000 строк), который содержит много объединенных ячеек. Я хочу импортировать его в R с readxl. Readxl разделяет объединенные ячейки, помещает значение в первую ячейку разделения и NA во все остальные. Но совершенно пустой ячейке также будет присвоен NA, поэтому я подумал, что лучше всего выяснить, какие ячейки пусты с помощью Excel, чтобы я знал, какие NA указывают на объединенную или пустую ячейку. Любые предложения о том, как решить эту проблему, очень приветствуются, спасибо!

Редактировать: Чтобы уточнить: В идеале я хочу объединить все ячейки в моем документе и заполнить каждую ячейку с разбивкой содержимым ранее объединенной ячейки. Но я нашел в сети макросы, которые должны были делать именно это, но они не работали с моим файлом, поэтому я подумал, что могу просто определить пустые ячейки и затем работать с ними в R. Обычно я не работаю с Excel поэтому я очень мало знаю об этом, извините, если мой мыслительный процесс слишком сложен.

Чтобы сделать именно то, что вы указали в заголовке:

Но есть и методы Excel для определения объединенных ячеек и воздействия на них. Итак, я не уверен, что именно вы хотите делать с информацией.

Если добавить к тому, что вы хотите сделать с результатами, возможно, этот код VBA может помочь:

Обратите внимание, что я явно объявляю все переменные, и я жестко закодировал диапазон для проверки. Существуют различные способы объявления диапазона, подлежащего проверке; Использование «Выбор» обычно редко предпочитается.

Прежде всего: с противоположного конца спектра вы можете использовать Range.MergeCells или Range.MergeArea чтобы определить, является ли ячейка частью объединенной области. Но я отвлекся .

Sub не может вернуть что-либо после вызова, поэтому вы можете вместо этого Function , например, Public Function EmptyRed() As String

(Кроме того, я бы порекомендовал вам привыкнуть явно объявлять все ваших переменных и, возможно, также использовать Option Explicit , прежде чем столкнетесь с ошибкой, основанной на опечатке. Просто добавьте > сейчас наверху саба)

Есть несколько способов:

PS: я отредактировал код на своем телефоне и буду отлаживать дальше, когда у меня есть компьютер. И я просто более привык использовать «диапазон», а не «клетка».

_{Чтобы уточнить: в идеале я хочу объединить все ячейки в моем документе и заполнить каждую разделенную ячейку содержимым ранее объединенной ячейки.}

Строка написана правильно. Ошибка возникает потому, что Intersect().Offset(,) равно нулю, пустоте и т.д.
Вопрос: Что это за строка и ошибка? Они у меня часто появляются. И можно ли написать условие, типа:

__________________
Помощь в написании контрольных, курсовых и дипломных работ здесь

SQL через ODBC: проверить перед вытаскиванием sqlread[0] что столбец не пуст
Имеется БД Access, беру оттуда строки. OdbcConnection connect = new.

Как деактивировать ячейку, не потеряв при этом выбранный диапазон
Здравствуйте! Нужна помощь. Но основе выбранного (вручную) диапазона нужно построить график с.

Как проверить пуст ли массив?
это продолжение темы. интересует грамотность записи If Массив(0) = "" Then ' вот эта проверка мне.

Как попросить макрос обрабатывать только выбранный диапазон, а не весь документ
Здравствуйте! Помогите, пожалуйста, переделать макрос, чтобы он работал только с выбранным участком.

Ход мыслей правильный
Дело в том, что аргументом метода Intersect не может быть Nothing. Попробуйте так: Дело в том, что аргументом метода Intersect не может быть Nothing. Попробуйте так: Спасибо большое! Все работает! Этак я скоро и на VBA научусь программировать))) Дело в том, что аргументом метода Intersect не может быть Nothing.

Что не так? Предложение If. Is Nothing Then, при любых значениях в диапазоне "F5:F1000" (как текстовых, так и числовых), всегда ложно. Т.е. после Then ничего не выполняется.

Добавлено через 10 минут
Эта конструкция тоже не помогает:

Range("F5:F1000").Value - это массив. Массив не может быть аргументом метода Find.
Объясните словами, что Вы хотите сделать. Мне надо скрыть столбец, если некоторый диапазон Range этого столбца - пустой.

Как грамотно проверить: пуст ли массив?
Создал массив, инициализировал его. Нужно его заполнить сведениями из другого массива, который.

Как проверить в с++ пуст файл или нет?
Добрый день! Мне нужно написать программу, которая после открытия файла делает проверку, пуст этот.

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

Проверить, что введенные числа входят в диапазон с 1 по 31
Всем привет! Народ подскажите, что-то мозгов не хватает.. Как написать функцию которая на вход.

Как выделить диапазон ячеек?
Объясните ламеру как можно вставить в функцию дапазон "DNosaukums" если известны первая и последняя.

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 может использоваться для одной ячейки или для диапазона ячеек.

Запрос значения ячейки

Следующая процедура запрашивает значение у пользователя и вставляет его в ячейку А1:

Sub GetValuel()
Range( " A1 " ).Value = InputBox( " Введите значение " )
End Sub

Однако при выполнении этой процедуры возникает проблема. Если пользователь щелкнет на кнопке Отмена в окне ввода данных, то процедура удалит данные, которые находились в текущей ячейке. Модифицированная версия процедуры адекватно реагирует на щелчок на кнопке Отмена и не выполняет при этом никаких действий:

Sub GetValue2()
Dim UserEntry As Variant
UserEntry = InputBox( " Введите значение " )
If UserEntry <> " " Then Range( " A1 " ).Value = UserEntry
End Sub

Во многих случаях следует проверить правильность данных, введенных пользователем. Например, необходимо обеспечить введение только чисел в диапазоне от 1 до 12 (рис. 2). Это можно сделать при помощи процедуры GetValue3(), код которой приведен в Модуле1 приложенного Excel-файла. Некорректные данные игнорируются, и окно запроса значения отображается снова. Этот цикл будет повторяться, пока пользователь не введет правильное значение или не щелкнет на кнопке Отмена.

Рис. 2. Проверка данных, введенных пользователем

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

Если требуется ввести значение в следующую пустую ячейку столбца или строки, используйте код (рис. 3):

Sub GetData()
Dim NextRow As Long
Dim Entry1 As String, Entry2 As String
Do
' Определение следующей пустой строки
NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
' Запрос данных
Entry1 = InputBox( " Введите имя " )
If Entry1 = " " Then Exit Sub
Entry2 = InputBox( " Введите сумму " )
If Entry2 = " " Then Exit Sub
' Запись данных
Cells(NextRow, 1) = Entry1
Cells(NextRow, 2) = Entry2
Loop
End Sub

Рис. 3. Макрос вставляет данные в следующую пустую строку рабочего листа

Это бесконечный цикл. Для выхода из него (щелкните на кнопке Cancel) использовались операторы Exit Sub. Обратите внимание строку, в который определяется значение переменной NextRow. Если вам трудно ее понять, проанализируйте содержимое ячейки: перейдите в последнюю ячейку столбца А и нажмите <End> и <↑>. После этого будет выделена последняя непустая ячейка в столбце А. Свойство Row возвращает номер этой строки; чтобы получить расположенную под ней строку (следующую пустую строку), к этому номеру прибавляется 1.

Приостановка работы макроса для определения диапазона пользователем

В некоторых ситуациях макрос должен взаимодействовать с пользователем. Например, можно создать макрос, который приостанавливается, когда пользователь указывает диапазон ячеек. Для этого воспользуйтесь функцией Excel InputBox. Не путайте метод Excel InputBox с функцией VBA InputBox. Несмотря на идентичность названий, это далеко не одно и то же.

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

Sub GetUserRange()
Dim UserRange As Range
Prompt = " Выберите диапазон для случайных чисел. "
Title = " Выбор диапазона "
' Отображение поля ввода
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ' Выделение диапазона
On Error GoTo 0
' Отменено ли отображение поля ввода?
If UserRange Is Nothing Then
MsgBox " Отменено. "
Else
UserRange.Formula = " =RAND() "
End If
End Sub

Окно ввода данных показано на рис. 4. Важный момент в этой процедуре – определение аргумента Туре равным 8 (в этом случае InputBox вернет диапазон; подробнее см. Application.InputBox Method).

Рис. 4. Использование окна ввода данных с целью приостановки выполнения макроса

Обязательно проверьте, включено ли обновление экрана при использовании метода InputBox для выделения диапазона. Если обновление экрана отключено, вы не сможете выделить рабочий лист. Чтобы проконтролировать обновление экрана, в процессе выполнения макроса используйте свойство ScreenUpdating объекта Application.

Подсчет выделенных ячеек

Если активный лист содержит диапазон data, то следующий оператор присваивает количество ячеек в диапазоне data переменной с названием CellCount:

CellCount = Range( " data " ).Count

Вы можете также определить, сколько строк или столбцов содержится в диапазоне. Следующее выражение вычисляет количество столбцов в выделенном диапазоне:

Следующий оператор пересчитывает количество строк в диапазоне с названием data и присваивает это количество переменной RowCount.

RowCount = Range( " data " ).Rows.Count

Просмотр выделенного диапазона

Вы можете столкнуться с трудностями при создании макроса, который оценивает каждую ячейку в диапазоне и выполняет операцию, определенную заданному критерию. Если выделен целый столбец или строка, то работа макроса может занять много времени. Процедура ColorNegative устанавливает красный цвет для ячеек, которые содержат отрицательные значения. Цвет фона для других ячеек не определяется. Код процедуры можно найти в Модуле4 приложенного Excel-файла.

Усовершенствованная процедура ColorNegative2, создает объектную переменную WorkRange типа Range, которая представляет собой пересечение выделенного диапазона и диапазона рабочего листа (рис. 5). Если выделить столбец F (1048576 ячеек), то его пересечение с рабочим диапазоном В2:I16) даст область F2:F16, которая намного меньше исходного выделенного диапазона. Время, затрачиваемое на обработку 15 ячеек, намного меньше времени, уходящего на обработку миллиона ячеек.

Рис. 5. В результате пересечения используемого диапазона и выделенного диапазона рабочего листа уменьшается количество обрабатываемых ячеек

И всё же процедура ColorNegative2 недостаточно эффективна, поскольку обрабатывает все ячейки в диапазоне. Поэтому предлагается процедура ColorNegative3. В ней используется метод SpecialCells, с помощью которого генерируются два поднабора выделенной области: один поднабор (ConstantCells) включает ячейки, которые содержат исключительно числовые константы; второй поднабор (FormulaCells) включает ячейки, содержащие числовые формулы. Обработка ячеек в этих поднаборах осуществляется с помощью двух конструкций For Each-Next. Благодаря тому, что исключается обработка пустых и нетекстовых ячеек, скорость выполнения макроса существенно увеличивается.

Sub ColorNegative3()
' Окрашивание ячеек с отрицательными значениями в красный цвет
Dim FormulaCells As Range, ConstantCells As Range
Dim cell As Range
If TypeName(Selection) <> " Range " Then Exit Sub
Application.ScreenUpdating = False
' Создание поднаборов исходной выделенной области
On Error Resume Next
Set FormulaCells = Selection.SpecialCells(xlFormulas, xlNumbers)
Set ConstantCells = Selection.SpecialCells(xlConstants, xlNumbers)
On Error GoTo 0
' Обработка ячеек с формулами
If Not FormulaCells Is Nothing Then
For Each cell In FormulaCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
' Обработка ячеек с константами
If Not ConstantCells Is Nothing Then
For Each cell In ConstantCells
If cell.Value < 0 Then
cell.Interior.Color = RGB(255, 0, 0)
Else
cell.Interior.Color = xlNone
End If
Next cell
End If
End Sub

Оператор On Error необходим, поскольку метод SpecialCells генерирует ошибку, если не находит в диапазоне ячеек указанного типа.

Удаление всех пустых строк

Следующая процедура удаляет все пустые строки в активном рабочем листе. Она достаточно эффективна, так как не проверяет все без исключения строки, а просматривает только строки в так называемом «используемом диапазоне», определяемом с помощью свойства UsedRange объекта Worksheet.

Первый шаг — определить последнюю используемую строку и присвоить этот номер строки переменной LastRow. Это не так просто, как можно ожидать, поскольку текущий диапазон необязательно начинается со строки 1. Следовательно, значение LastRow вычисляется таким образом: к найденному количеству строк используемого диапазона прибавляется номер первой строки текущего диапазона и вычитается 1.

В процедуре применена функция Excel СЧЁТЗ, определяющая, является ли строка пустой. Если данная функция для конкретной строки возвращает 0, то эта строка пустая. Обратите внимание, что процедура просматривает строки снизу вверх и использует отрицательное значение шага в цикле For-Next. Это необходимо, поскольку при удалении все последующие строки перемещаются «вверх» в рабочем листе. Если бы в цикле просмотр выполнялся сверху вниз, то значение счетчика цикла после удаления строки оказалось бы неправильным.

Дублирование строк

Пример, рассматриваемый в этом разделе, демонстрирует использование возможностей VBA для создания дубликатов строк. На рис. 6 показан пример рабочего листа, используемого организаторами лотереи. В столбце А вводится имя. В столбце В содержится количество лотерейных билетов, приобретенных одним покупателем. В столбце С находится случайное число сгенерированное с помощью функции СЛЧИС. Победитель определяется путем сортировки данных в третьем столбце (выигрыш соответствует наибольшему случайному числу).

Рис. 6. Дублирование строк на основе значений в столбце В

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

Объектная переменная cell была инициализирована ячейкой В2, первой ячейкой, в которой находится числовая величина. Вставка новых строк осуществляется в цикле, а их копирование происходит с помощью метода FillDown. Значение переменной cell увеличивается на единицу, после чего выбирается следующий участник лотереи, Цикл выполняется до тех пор, пока не встретится пустая ячейка. На рис. 7 показан рабочий лист после выполнения этой процедуры.

Рис. 7. В соответствии со значением в столбце В добавлены новые строки

Определение диапазона, находящегося в другом диапазоне

Функция InRange имеет два аргумента, оба — объекты Range. Функция возвращает значение True (Истина), если первый диапазон содержится во втором.

Возможно, функция InRange кажется сложнее, чем того требует ситуация, поскольку в коде должна быть реализована проверка принадлежности двух диапазонов одной и той же книге и рабочему листу. Обратите внимание, что в процедуре используется свойство Parent, которое возвращает объект-контейнер заданного объекта. Например, следующее выражение возвращает название листа для объекта rng1:

Следующее выражение возвращает название рабочей книги rng1:

Функция VBA Union возвращает объект Range, который представляет собой объединение двух объектов типа Range. Объединение содержит все ячейки, относящиеся к исходным диапазонам. Если адрес объединения двух диапазонов совпадает с адресом второго диапазона, первый диапазон входит в состав второго диапазона.

Определение типа данных ячейки

В состав Excel входит ряд встроенных функций, которые могут помочь определить тип данных, содержащихся в ячейке. Это функции ЕНЕТЕКСТ, ЕЛОГИЧ и ЕОШИБКА. Кроме того, VBA поддерживает функции IsEmpty, IsDate и IsNumeric.

Ниже описана функция CellType, которая принимает аргумент-диапазон и возвращает строку, описывающую тип данных левой верхней ячейки этого диапазона (рис. 8). Такую функцию можно использовать в формуле рабочего листа или вызвать из другой процедуры VBA.

Рис. 8. Функция CellType, возвращающая тип данных ячейки

Function CellType(Rng)
' Возвращает тип ячейки, находящейся в левом верхнем углу диапазона
Dim TheCell As Range
Set TheCell = Rng.Range( " A1 " )
Select Case True
Case IsEmpty(TheCell)
CellType = " Пустая "
Case TheCell.NumberFormat = " @ "
CellType = " Текст "
Case Application.IsText(TheCell)
CellType = " Текст "
Case Application.IsLogical(TheCell)
CellType = " Логический "
Case Application.IsErr(TheCell)
CellType = " Ошибка "
Case IsDate(TheCell)
CellType = " Дата "
Case InStr(1, TheCell.Text, " : " ) <> 0
CellType = " Время "
Case IsNumeric(TheCell)
CellType = " Число "
End Select
End Function

Обратите внимание на использование оператора SetTheCell. Функция CellType получает аргумент-диапазон произвольного размера, но этот оператор указывает, что функция оперирует только левой верхней ячейкой диапазона (представленной переменной TheCell).

Читайте также: