Ребята, всем привет! 👋 Продолжаем изучать Excel.
Иногда есть необходимость перед какими-то действиями сначала очистить лист Excel от всех данных, которые на нем есть, чтобы заполнить его новыми данными.
В этом уроке рассмотрим несколько вариантов решения позволяющих очистить лист при помощи VBA.
✍️ Если вы только начинаете осваивать VBA Excel мы уверены, каждый может найти для себя что-то новое!
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
Пусть имеем некоторые данные на листе "Лист1"
Рассмотрим примеры решения позволяющие очистить лист Excel при помощи VBA.
📢 ВАЖНО! ПОМНИТЕ - Вы не можете отменить изменения, внесенные кодом VBA, поэтому мы рекомендуем вам сохранить резервную копию вашего набора данных перед запуском кода.
⏩ ВАРИАНТ 1. Очистка всех ячеек на листе включая форматирование
Для очистки всех ячеек на листе, а так же оформления ячеек (шрифт, фон и т.п.) можно использовать следующий код:
В данном случае использован Метод Clear (Очистить).
Этот метод удаляет все объекты, входящие в некоторый объект или коллекцию объектов. Из списка и комбинированного списка Clear удаляет все элементы списков, т. е. делает их пустыми. Из элементов набор страниц и полоса вкладок им удаляются все страницы и вкладки.
Из коллекции элементов управления Controls метод Clear удаляет элементы, созданные при исполнении программы методом Add.
В результате будет выполнена очистка данных включая форматирование:
⏩ ВАРИАНТ 2. Очистка всех ячеек на листе не затрагивая форматирование
Если нужно очистить только данные на листе Excel, не затрагивая формат ячеек, можно сделать так
Заменяем Метод Clear (Очистить) на ClearContents
Метод ClearContents очищает формулы и значения, содержащиеся в ячейках, представляемых объектом Range.
Данный метод очищает только содержимое ячеек, сохраняя их форматирование.
В результате будет выполнена очистка данных, но сохранено форматирование:
⏩ ВАРИАНТ 3.
Данный вариант имеет место и позволяет очистить все данные и при условии, что определение последней строки SpecialCells(xlCellTypeLastCell) работало правильно.
Метод SpecialCells используется для выделения определенных ячеек на основании оценки их содержимого или других характеристик. Применяемое здесь значение параметра-константы xlCellTypeLastCell указывает методу выделить последнюю ячейку используемого диапазона, т.е. саму нижнюю правую ячейку в диапазоне, где введено хоть какое-то значение.
Пример кода:
Свойство Saved (возвращает/устанавливает ) состояние сохранения рабочей книги:
- если пользователь внес изменения в структуру или содержимое рабочей книги, свойство Saved имеет значение True.
- если свойству Saved программно присвоено False, то Excel будет считать, что ваша рабочая книга уже сохранена, и не станет предлагать сохранить ее еще раз.
Аналогично первому примеру в результате будет выполнена очистка данных включая форматирование:
⏩ ВАРИАНТ 4. Метод удаления листа
Метод удаления листа в VBA используется для удаления листа из книги Excel. Когда мы удаляем рабочий лист, метод удаления рабочего листа отображает диалоговое окно, которое напоминает пользователю подтвердить удаление по умолчанию. Если мы нажмем на кнопку удаления в диалоговом окне, то рабочий лист будет удален из рабочей книги. Он имеет логическое значение.
Это означает, что появится диалоговое окно с двумя вариантами. Это либо отмена (False), либо Удаление (True).
Если лист содержит очень преочень много данных, тогда чтобы очистить лист Excel самым быстрым способом будет удалить его и создать заново.
Пример кода:
В результате Лист1 будет удален и с помощью метода Add добавлен новый чистый лист.
Метод Sheets.Add создает новый рабочий лист, лист диаграммы или макроса. Новый лист становится активным.
В дополнение ...
Возможно вы столкнетесь с ситуацией когда потребуется очистка данных начиная с определенной строки (например потребуется оставить шапку таблицы, или удалить все кроме первой строки и т.п). В данной ситуации можно использовать код приведенный ниже.
В данном примере использовано Свойство Resize и EntireRow объекта Range
Свойство Resize объекта Range позволяет в коде VBA Excel изменять размер указанного диапазона.
Свойство Range.Resize устанавливает новый размер исходного диапазона по указанному количеству строк и столбцов, а свойство ячейки EntireRow Возвращает всю строку (строки), в которой содержится ячейка (диапазон).
Пример кода:
В результате будет выполнена очистка всех данных кроме первой строки:
А на этом сегодня все. 👏 Теперь вы знаете как очистить лист Excel при помощи VBA. Предложенных вариантов удаления всех данных на листе Excel полагаем будет достаточно для решения повседневных задач на VBA.
Если вы используете иной метод позволяющий очистить лист при помощи VBA поделитесь с нами в комментариях.
Я надеюсь, что вы нашли этот урок полезным. Продолжение следует...
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Сэкономьте сумасшедшее количество времени, используя VBA. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
Понравился урок!? не забываем оставлять комментарий 📝Нам очень важна Ваша обратная связь 💝
И конечно же, за лайк 👍 благодарочка 💖 и респект 🤝 каждому!
