Vba excel очистить диапазон ячеек
Обычно на листе Excel у нас есть два разных метода удаления строк, один из которых является сочетанием клавиш, а другой — с помощью метода щелчка правой кнопкой мыши и вставки, но в VBA мы должны использовать команду удаления и оператор рабочего листа, чтобы удалить все строки вместе, хитрость это то, что если нам нужно удалить одну строку, мы даем ссылку на одну строку, но для нескольких столбцов мы даем несколько ссылок на строку.
Используя метод удаления строки VBA, мы можем удалить все пустые строки и удалить строку на основе значения ячейки. Мы также можем удалить всю строку, если какая-либо из ячеек пуста.
В этой статье мы обсудим метод «Удалить строку VBA». Займите себя чем-то в течение следующих 15-20 минут, чтобы узнать об этой концепции.
Как удалить строку?
Пример №1
В VBA нам нужно указать строку, которую мы удаляем.
Код:
Ячейки (1, 1) означают первый столбец первой строки, т. е. ячейку A1. Затем используем метод «удалить».
Код:
Теперь это удалит первую ячейку. Все значения правой стороны сместятся на одну ячейку влево.
Если вы хотите удалить всю строку, нам нужно использовать свойство «EntireRow», тогда нам нужно использовать метод «удалить», чтобы удалить всю строку выбранной нами ячейки.
Код:
Например, я ввел несколько символов на листе Excel следующим образом.
Теперь, если я запущу этот код, он удалит всю строку, а не одну ячейку.
Пример №3
Мы можем удалить строку несколькими способами. В приведенном выше примере мы удалили строку с помощью свойства CELLS. Теперь мы увидим, как удалить с помощью свойства ROWS.
Теперь нам нужно упомянуть, какую строку нам нужно удалить. Допустим, нам нужно удалить 5 й ряд.
Теперь используйте свойство «EntireRow».
После выбора свойства, что нам нужно сделать, т.е. метод. Нам нужно удалить строку.
Код:
Итак, этот код удалит 5 й ряд.
Пример №4
Удалить несколько строк с помощью объекта диапазона
Как удалить несколько строк?
Мы можем использовать объект VBA RANGE Используйте объект VBA RANGE Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте рабочего листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее чтобы удалить более одной строки. Предположим, у вас есть значения от ячеек от A1 до A6.
Теперь я хочу удалить первые 5 строк, поэтому я могу ссылаться на эти строки, используя объект Range как «Диапазон («A1: A5»)»
Код:
Теперь я хочу использовать свойство слова «EntireRow».
Код:
Во всей этой строке нам нужно выполнить метод удаления, поэтому используем метод Delete.
Код:
Теперь это удалит выбранные строки.
Пример №5
Удалить строки на основе значения ячейки
Мы также можем использовать это «Вся строка. Удалить» метод удаления строки на основе значение ячейки в VBA Значение ячейки в VBA В VBA есть два способа взаимодействия с ячейкой или получения значения из нее: метод диапазона и метод ячейки. читать далее . Например, у меня есть значения «Да» и «Нет» из ячеек от A1 до A10.
Теперь нам нужно удалить строки со значением «Нет». Чтобы выполнить эту задачу, нам нужно использовать функцию «ЕСЛИ» с циклами, чтобы удалить все строки со значением «Нет».
Приведенный ниже код сделает эту работу за нас.
Код:
Пример №6
Удалить все строки пустых ячеек
Бывают ситуации, когда нам нужно удалить всю строку, если какая-либо из ячеек в диапазоне пуста. Например, у меня есть набор данных ниже.
Все цветные ячейки пусты, поэтому мне нужно удалить всю строку. Мы можем выполнить эту задачу с помощью двух наборов кода. Ниже приведен код.
Код:
Это определит пустые ячейки в диапазоне от A1 до F10. Если будут найдены какие-либо пустые ячейки, они удалят всю строку.
Проблема с этим кодом заключается в том, что он удалит строку пустой ячейки только в диапазоне от A1 до F10, но если какие-либо ячейки пусты в любых других ячейках, он не будет удален. Помня об этом, я написал еще один код.
Код:
Когда вы запускаете этот код, во-первых, он попросит вас выбрать диапазон с полем ввода, появляющимся перед вами.
После выбора диапазона нужно нажать на ОК. Будет удалить все строки пустых ячеек Удалить все строки пустых ячеек Существует несколько способов удаления пустых строк из Excel: 1) Удаление пустых строк вручную, если пустых строк мало 2) Использование формулы удаления 3) Использование фильтра для поиска и удаления пустых строк. читать далее в выбранном диапазоне.
Допустим, у нас есть два открытых файла: «Книга1» и «Книга2», причем, файл «Книга1» активен и в нем находится исполняемый код VBA.
В общем случае при обращении к ячейке неактивной рабочей книги «Книга2» из кода файла «Книга1» прописывается полный путь:
Удобнее обращаться к ячейке через свойство рабочего листа Cells(номер строки, номер столбца), так как вместо номеров строк и столбцов можно использовать переменные. Обратите внимание, что при обращении к любой рабочей книге, она должна быть открыта, иначе произойдет ошибка. Закрытую книгу перед обращением к ней необходимо открыть.
Теперь предположим, что у нас в активной книге «Книга1» активны «Лист1» и ячейка на нем «A1». Тогда обращение к ячейке «A1» можно записать следующим образом:
Точно также можно обращаться и к другим ячейкам активного рабочего листа, кроме обращения ActiveCell, так как активной может быть только одна ячейка, в нашем примере – это ячейка «A1».
Если мы обращаемся к ячейке на неактивном листе активной рабочей книги, тогда необходимо указать этот лист:
Имя ярлыка может совпадать с основным именем листа. Увидеть эти имена можно в окне редактора VBA в проводнике проекта. Без скобок отображается основное имя листа, в скобках – имя ярлыка.
Обращение к ячейке по индексу
К ячейке на рабочем листе можно обращаться по ее индексу (порядковому номеру), который считается по расположению ячейки на листе слева-направо и сверху-вниз.
Например, индекс ячеек в первой строке равен номеру столбца. Индекс ячеек во второй строке равен количеству ячеек в первой строке (которое равно общему количеству столбцов на листе, зависящему от версии Excel) плюс номер столбца. Индекс ячеек в третьей строке равен количеству ячеек в двух первых строках плюс номер столбца. И так далее.
Для примера, Cells(4) та же ячейка, что и Cells(1, 4). Используется такое обозначение редко, тем более, что у разных версий Excel может быть разным количество столбцов и строк на рабочем листе.
По индексу можно обращаться к ячейке не только на всем рабочем листе, но и в отдельном диапазоне. Нумерация ячеек осуществляется в пределах заданного диапазона по тому же правилу: слева-направо и сверху-вниз. Вот индексы ячеек диапазона Range(«A1:C3»):
Обращение к ячейке Range("A1:C3").Cells(5) соответствует выражению Range("B2") .
Обращение к ячейке по имени
Если ячейке на рабочем листе Excel присвоено имя (Формулы –> Присвоить имя), то обращаться к ней можно по присвоенному имени.
Допустим одной из ячеек присвоено имя – «Итого», тогда обратиться к ней можно – Range("Итого") .
Запись информации в ячейку
Содержание ячейки определяется ее свойством «Value», которое в VBA Excel является свойством по умолчанию и его можно явно не указывать. Записывается информация в ячейку при помощи оператора присваивания «=»:
Диапазон — это свойство в VBA похоже на свойство рабочего листа, свойство диапазона также имеет множество приложений и применений, когда мы пишем наш код и указываем конкретный диапазон ячеек или конкретную ячейку, это делается методом свойства диапазона, оно используется для ссылки на строки ячеек и столбцы.
Как вы знаете, VBA используется для записи и запуска макросов и автоматизации задач Excel, а также для более быстрого и точного выполнения повторяющихся задач.
В контексте рабочего листа Excel объект диапазона VBA обозначает ячейки, как одиночные, так и множественные. Объект диапазона может включать одну ячейку, всю строку или столбец или несколько ячеек, распределенных по строкам и столбцам.
Чтобы VBA запускал макросы и выполнял задачи, ему необходимо определить ячейки, в которых должны выполняться вызываемые задачи. Именно здесь концепция Range Objects находит свое применение.
Как использовать объект Range?
Чтобы ссылаться на объекты в VBA, мы используем иерархическую технику. Есть 3 иерархии:
- Квалификатор объекта: Он относится к местоположению объекта, например, где он находится, т. Е. К книге или листу, на которые имеется ссылка.
- Два других используются для манипулирования значениями ячеек. Это свойство и методы.
- Свойство: Здесь хранится информация об объекте.
- Метод: Это относится к действию, которое объект будет выполнять.
Например, для Range методом будут такие действия, как сортировка, форматирование, выбор, очистка и т. Д.
Это структура, которой следуют всякий раз, когда упоминается объект VBA. Эти 3 разделены точкой (.)
Application.Workbooks.Worksheets.Range
синтаксис
Application.Workbooks («Booknew.xlsm»). Рабочие листы («Sheet3»). Range («B1»)
Примеры
Пример №1 — Обращение к отдельной ячейке
Предположим, нам нужно выбрать ячейку «B2» в «sheet1» в книге.
Выполните следующие шаги:
- Откройте Excel. Откройте файл с расширением Excel «.xlsm», что означает «Книга с поддержкой макросов Excel». Книга Excel с типами «.xlsx» не позволит вам сохранить макросы, которые вы будете писать сейчас.
- Теперь, когда вы открыли книгу, вам нужно перейти в редактор VBA. Вы можете использовать сочетание клавиш «ALT + F11», чтобы открыть редактор, или воспользуйтесь приведенным ниже методом, как показано на снимке экрана:
Вы увидите экран, похожий на показанный ниже:
Теперь напишите код, как показано на скриншоте ниже.
Смотрите на скриншоте Excel ниже, что в настоящее время активирована ячейка A2. После запуска кода обратите внимание, где находится активированная ячейка.
Запустите код, как показано на скриншоте ниже:
Совет: вы также можете использовать горячую клавишу Excel, например F5, для запуска кода.
Вы увидите, что ячейка «B2» выбрана после выполнения программы.
Здесь вы даете инструкции программе перейти к определенной ячейке на определенном листе конкретной книги и выполнить действие, указанное здесь, для выбора.
Точно так же вы можете использовать синтаксис для выбора широкого спектра ячеек и диапазонов, а также выполнять с ними различные действия.
Например, здесь, чтобы выбрать вторую строку. Запустите приведенный ниже код, чтобы выбрать всю строку
Здесь диапазон («2: 2») означает вторую строку. Вы можете вернуться к своему листу Excel и увидеть результаты, как показано на скриншоте ниже.
Например, здесь, чтобы выбрать весь столбец C. Запустите приведенный ниже код и просмотрите результаты.
После ввода приведенного выше кода вы увидите, что весь столбец выбран на вашем листе Excel. См. Снимок экрана ниже.
Здесь диапазон («C: C») означает столбец C.
Точно так же вы можете выбрать непрерывные ячейки или несмежные ячейки, пересечение диапазонов ячеек и т. Д.
Просто внесите следующие изменения в часть диапазона, показанную в коде.
Пример №4 — Выбор смежных ячеек: диапазон («B2: D6»)
Пример № 5 — Выбор несмежных ячеек: диапазон («B1: C5, G1: G3»)
Пример №6 — Выбор пересечения диапазона: Диапазон («B1: G5 G1: G3»)
[Note the absence of comma here]. Здесь вы увидите, что выбираются от G1 до G3, которые являются общими ячейками в указанном диапазоне.
Теперь следующим примером будет выбор группы ячеек на листе и объединение их в одну ячейку.
Предположим, вы хотите объединить ячейки «B1: C5» в одну. См. Приведенный ниже код и следуйте инструкциям.
Здесь «.merge» — это действие, которое мы выполняем над группой ячеек, заданной в диапазоне.
Предположим, что ячейки «F2: H6» выделены желтым, и мы хотим очистить это форматирование Excel. Другой сценарий, возможно, вы хотите удалить все форматирование либо на всем листе, либо в группе ячеек.
Смотрите скриншоты ниже, чтобы продолжить. Сначала я покажу вам отформатированные ячейки (F2: H6).
Пожалуйста, запустите коды, показанные на снимке экрана ниже, чтобы удалить это форматирование в выбранном диапазоне ячеек.
Синтаксис: ThisWorkbook.Worksheets («Sheet1»). Range («F2: H6»). ClearFormats
Вы можете обратиться к этому снимку экрана, приведенному ниже:
Точно так же вы можете очистить содержимое диапазона ячеек с помощью действия «.ClearContents».
Очистить содержимое — это метод в VBA который используется для удаления или удаления значений, которые хранятся в предоставленных ему ячейках, этот метод делает диапазон ячеек пустым и используется со свойством диапазона для доступа к указанному диапазону ячеек, пример использования этого метода следующий диапазон («A1: B2»). ClearContents этот метод очистит содержимое ячеек от A1 до B2.
В Excel добавление и удаление данных — обычная рутинная задача. Иногда мы удаляем одно значение ячейки, иногда много значений ячеек, а иногда нам может потребоваться удалить все содержимое рабочего листа. В этой статье мы покажем вам, как использовать метод «Очистить содержимое» в Excel VBA. В VBA есть много способов сделать это, например «Очистить», «Удалить» и «Очистить содержимое».
Что такое четкое содержимое в Excel VBA?
Прежде чем я расскажу вам об очистке содержимого в VBA, позвольте мне показать, как мы можем удалить или очистить данные в определенном диапазоне.
Например, посмотрите на данные ниже.
Теперь, если я хочу очистить ячейку от A1 до C3, нам нужно сначала упомянуть диапазон ячеек. используя ДИАПАЗОН VBA Использование диапазона VBA RANGE Диапазон — это свойство в VBA, которое помогает указать конкретную ячейку, диапазон ячеек, строку, столбец или трехмерный диапазон. В контексте листа Excel объект диапазона VBA включает одну или несколько ячеек, распределенных по различным строкам и столбцам. читать далее объект.
Код:
После упоминания диапазона ячеек с помощью объекта RANGE нам нужно выбрать метод «Очистить», чтобы убрать упоминание о значениях ячеек.
Код:
Это очистит указанные значения ячеек.
Код:
Помимо метода очистки, мы также можем использовать метод «УДАЛИТЬ».
Код:
Это приведет к удалению упомянутых значений ячеек, как это сделал наш метод очистки.
Если вы хотите удалить все данные ячейки, вы можете использовать VBA CELLS свойство Свойство VBA CELLS Ячейки — это ячейки рабочего листа, и в VBA, когда мы ссылаемся на ячейки как на свойство диапазона, мы ссылаемся на те же ячейки. В концепциях VBA ячейки также такие же, как и обычные ячейки Excel. читать далее с именем рабочего листа.
Оба приведенных выше кода удаляют все данные листа «Sheet1». Он удалит значения ячеек прямо от первой до последней ячейки рабочего листа.
Если вы хотите удалить существующие ячейки листа, вы можете использовать объект Active Sheet.
Разница между методами очистки и удаления
Я знаю, что этот вопрос уже должен был возникнуть у вас в голове.
Да, между этими двумя методами есть разница.
Когда вы используете метод «Удалить», он удаляет ячейку, а ячейка ниже примет положение удаленной ячейки.
Например, посмотрите на изображение ниже.
Теперь я буду использовать метод удаления, чтобы удалить ячейку A1.
Код:
Я запущу этот код и посмотрю, что произойдет.
Посмотрите, что здесь произошло; как я сказал, когда я удалил ячейку A1, она удаляется, но ячейка A2 перемещается на одну ячейку вверх и занимает удаленную ячейку. Это приведет к несоответствию данных. Так что будьте осторожны при использовании метода удаления.
Теперь для тех же данных я проясню метод.
Код:
Теперь посмотрим, что происходит, когда я запускаю этот код.
Этот код только что освободил ячейку A1 без изменения других ячеек. Это выглядит правильным методом для удаления только части ячеек всего диапазона данных.
Используйте метод очистки содержимого VBA для сохранения форматирования ячеек
Если вы наблюдали за двумя предыдущими методами, эти два метода не только удаляли или очищали предоставленные ячейки. Он также удалил форматирование ячеек, которое мы предоставили.
Чтобы сохранить форматирование ячеек, нам не нужно использовать ни «Удалить», ни «Очистить», но нам нужно использовать метод VBA «Очистить содержимое».
В тот момент, когда вы вводите диапазон ячеек с помощью объекта RANGE, он покажет все свойства и методы, связанные с ним.
Мы можем получить доступ к «Удалить», мы можем получить доступ к «Очистить», а также можем получить доступ к методам «ClearContents».
Выберите этот метод.
Код:
Теперь это очистит содержимое от ячейки A1 до C3, но у нас будет все существующее форматирование как есть.
Как вы можете видеть на картинке выше, у нас есть цвет ячеек в VBA, границы и каждое форматирование, связанное с этими упомянутыми ячейками.
Точно так же мы можем очистить содержимое других листов.
Это очистит содержимое ячеек с A1 по D10 на листе «Sheet1».
Точно так же мы можем удалить и другие открытые ячейки книги.
Прокрутите все рабочие листы и очистите содержимое определенного диапазона
Предположим, у вас много листов в вашей книге, и вы хотите удалить диапазон ячеек от A1 до C15 на всех листах, которые нам нужны. Для каждого цикла в VBA Для каждого цикла в VBA VBA For Each Loop помогает пользователю проверять и анализировать группы объектов или значений индивидуально. Он даже облегчает выполнение определенного действия для каждого объекта или значения, передавая оператор или группу операторов в этой ссылке. читать далее сделать это.
Приведенный ниже код выполнит эту работу.
Код:
На всякий случай, если вы хотите удалить все данные рабочего листа, вам необходимо использовать приведенный ниже код.
Код:
Вы можете скачать этот шаблон VBA Clear Contents Excel здесь — Шаблон очистки содержимого VBA.
Имеем диапазон ячеек с данными, в котором есть пустые ячейки:
Задача - удалить пустые ячейки, оставив только ячейки с информацией.
Способ 1. Грубо и быстро
- Выделяем исходный диапазон
- Жмем клавишу F5, далее кнопка Выделить(Special) . В открывшмся окне выбираем Пустые ячейки(Blanks) и жмем ОК.
Способ 2. Формула массива
Для упрощения дадим нашим рабочим диапазонам имена, используя Диспетчер Имен (Name Manager) на вкладке Формулы (Formulas) или - в Excel 2003 и старше - меню Вставка - Имя - Присвоить (Insert - Name - Define)
Диапазону B3:B10 даем имя ЕстьПустые, диапазону D3:D10 - НетПустых. Диапазоны должны быть строго одного размера, а расположены могут быть где угодно относительно друг друга.
Теперь выделим первую ячейку второго диапазона (D3) и введем в нее такую страшноватую формулу:
В английской версии это будет:
=IF(ROW()-ROW(НетПустых)+1>ROWS(ЕстьПустые)-COUNTBLANK(ЕстьПустые),"",INDIRECT(ADDRESS(SMALL((IF(ЕстьПустые<>"",ROW(ЕстьПустые),ROW()+ROWS(ЕстьПустые))),ROW()-ROW(НетПустых)+1),COLUMN(ЕстьПустые),4)))
Причем ввести ее надо как формулу массива, т.е. после вставки нажать не Enter (как обычно), а Ctrl+Shift+Enter. Теперь формулу можно скопировать вниз, используя автозаполнение (потянуть за черный крестик в правом нижнем углу ячейки) - и мы получим исходный диапазон, но без пустых ячеек:
Способ 3. Пользовательская функция на VBA
Если есть подозрение, что вам часто придется повторять процедуру удаления пустых ячеек из диапазонов, то лучше один раз добавить в стандартный набор свою функцию для удаления пустых ячеек, и пользоваться ей во всех последующих случаях.
Для этого откройте редактор Visual Basic (ALT+F11), вставьте новый пустой модуль (меню Insert - Module) и скопируйте туда текст этой функции:
Не забудьте сохранить файл и вернитесь из редактора Visual Basic в Excel. Чтобы использовать эту функцию в нашем примере:
Читайте также: