Очистить лист Excel vba
Обновлено: 21.11.2024
Листы Excel являются частью книги или документа Excel. В книге может быть любое количество листов Excel. Лист Excel может иметь или не иметь какие-либо из следующих компонентов/содержимого:
- Данные в ячейках
- Отформатированные ячейки
- Цвет фона
- Шрифт
- Начертание шрифта: полужирный/курсив/подчеркнутый
- Цвет шрифта
- Границы ячеек (различные стили)
- Условное форматирование на основе содержимого ячейки
Очистка содержимого рабочего листа
Если нам нужно очистить содержимое, форматирование и другие данные с одного или нескольких листов в зависимости от каких-либо условий, делать это вручную может быть утомительно. В этом случае было бы здорово, если бы мы могли очистить лист с помощью заранее определенной программы. Здесь нам на помощь приходит VBA.
Очистка только содержимого ячейки
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор очищает только данные (текст) во всех ячейках листа с именем «Лист1». Он по-прежнему сохранит другие компоненты листа, такие как диаграммы, графики, формат ячеек и т. д.
Очистка только формата ячейки
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор очищает только форматирование всех ячеек листа с именем «Лист1». Данные и другие компоненты листа все равно будут сохранены.
Удалить используемый диапазон листа
Синтаксис
Этот оператор может удалить все содержимое и объекты активного листа в пределах используемого диапазона.
Очистить комментарии на листе
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор удалит только комментарии ко всем ячейкам на листе с именем «Лист1». Он по-прежнему сохранит данные, форматирование и другие компоненты листа.
Очистить гиперссылки на листе
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор очистит только гиперссылки всех ячеек на листе с именем «Лист1». Он по-прежнему сохранит данные, форматирование, комментарии и другие компоненты листа.
Очистить структуру на листе
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор очистит только структуру всех ячеек на листе с именем «Лист1». Он по-прежнему сохранит данные, форматирование, комментарии и другие компоненты листа.
Очистить примечания на листе
Синтаксис
Если имя листа не является обязательным и не указано, считается активным и открытым листом.
Пример
Этот оператор очищает только примечания ко всем ячейкам на листе с именем «Лист1». Он по-прежнему сохранит данные, форматирование, комментарии и другие компоненты листа.
Очистка всего листа
Если необходимо очистить все содержимое, включая данные, формат, фигуры и другие объекты, можно использовать свойство очистки ячеек (объект на листе).
Синтаксис
Где имя листа — это имя рабочего листа, на котором необходимо выполнить действие. Ячейки относятся ко всем ячейкам листа.
Вместо имени листа в двойных кавычках мы также можем указать порядковый номер листа без двойных кавычек
«Activesheet» также можно использовать вместо названия листа.
Пример:
Представим, что нам нужно очистить все комментарии/примечания/гиперссылки с 3-х листов, содержащих работу «Единица». Приведенный ниже код поможет нам быстро выполнить задачу.
Примечание. Это можно сделать на любом количестве листов.
На одном листе есть данные и границы.
На следующем листе под названием Unit 2 есть комментарий в одной из ячеек.
Есть еще один лист под названием «блок 15», на котором есть данные с условным форматированием.
Теперь мы используем этот код VBA, чтобы очистить все это на трех листах.
На этом изображении также показаны другие варианты очистки, описанные выше.
Объяснение кода:
Здесь мы объявляем необходимые переменные и инициализируем значения для книги, рабочего листа, счетчика и т. д.
Затем мы входим в цикл. Внутри цикла мы помещаем условие, чтобы проверить, содержит ли имя рабочего листа слово «Единица». Если это так, мы очищаем все содержимое листа.
При запуске кода мы видим, что все виды данных удаляются из всех трех форм.
Заключение
VBA напрямую сокращает время и усилия на очистку любых конкретных или всех видов данных (список компонентов рабочего листа) легко и быстро. Но стоит сказать, что нам нужно быть очень осторожными перед запуском скрипта в редакторе VBA, так как это может быть отменено/необратимо (т. е. однажды потерянные данные потеряны навсегда). Единственная возможность спастись от такой ситуации — сделать резервную копию книги, прежде чем пытаться что-либо делать в редакторе VBA.
Об авторе Лакшми Рамакришнан
Лакшми Рамакришнан – специалист по автоматизации с опытом работы тренером, архитектором решений, тестировщиком и разработчиком. Она создала инструменты VBA для самых разных целей, от бухгалтерского учета до ИТ, и с удовольствием делится своим опытом и знаниями, чтобы помочь новичкам.
Как очистить весь лист или указанный диапазон в Excel?
Эта статья покажет вам, как быстро очистить содержимое с форматированием ячеек или без него для всего листа или указанного диапазона с помощью кода VBA в Excel.
Очистить весь лист с кодом VBA в Excel
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Приведенные ниже коды VBA помогут вам очистить весь лист с форматированием ячеек или без него.
<р>1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений. <р>2. В окне Microsoft Visual Basic для приложений нажмите «Вставить» > «Модуль», а затем скопируйте приведенный ниже код VBA в модуль.Если вы хотите просто очистить содержимое ячейки, но сохранить форматирование, попробуйте этот код.
Код VBA: очистить весь лист без форматирования ячеек
Если вы хотите очистить как содержимое, так и форматирование ячейки, этот код может вам помочь.
Код VBA: очистить весь лист с форматированием ячеек
Примечание. В коде Sheet4 – это имя рабочего листа, содержимое которого будет удалено. Измените его по своему усмотрению.
<р>3. Нажмите клавишу F5 или нажмите кнопку "Выполнить", чтобы запустить код.Затем указанный рабочий лист с форматированием ячеек или без него немедленно очищается.
Очистить указанный диапазон с помощью кода VBA в Excel
Для очистки только указанного диапазона на листе вы можете попробовать следующий код VBA.
<р>1. Откройте рабочий лист, из которого нужно очистить указанный диапазон, затем нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений. <р>2. В окне Microsoft Visual Basic для приложений нажмите «Вставить» > «Модуль», а затем скопируйте приведенный ниже код VBA в модуль.Очистить диапазон, но сохранить форматирование, попробуйте этот код.
Код VBA: очистить указанный диапазон без форматирования ячейки
Очистить диапазон с форматированием ячейки, используйте этот код:
Код VBA: очистить указанный диапазон с помощью форматирования ячейки
Примечание. A1:C10 — это диапазон, который вы очистите на листе.
<р>3. Нажмите клавишу F5 или нажмите кнопку "Выполнить", чтобы очистить диапазон.Чтобы удалить лист с помощью VBA, необходимо использовать метод удаления VBA. Вам нужно указать лист, который вы хотите удалить, а затем использовать этот метод. Допустим, если вы хотите удалить «Лист1», вам нужно упомянуть лист1, а затем ввести точку (.) и в конце ввести «Удалить».
В этом руководстве мы рассмотрим различные способы удаления листа с помощью кода VBA. Убедитесь, что на ленте есть вкладка разработчика, отсюда вы можете попасть в редактор Visual Basic.
Удалить лист, используя его имя
У каждого листа есть имя, и вы можете написать код, чтобы удалить лист, используя это имя. Итак, допустим, вы хотите удалить рабочий лист «Данные», код будет таким:
- Когда вы удаляете рабочий лист, Excel показывает сообщение для подтверждения, хотите ли вы удалить его или хотите отменить. И когда вы используете код VBA, в этом случае Excel также сделает это.
Чтобы решить эту проблему, вы можете отключить обновление экрана, чтобы удалить лист, а затем включить его.
Теперь предположим, что вы хотите использовать значение ячейки для использования имени рабочего листа. В этом случае для этого вам нужно использовать объект диапазона VBA.
Удалить лист, используя номер листа
Верно. Вы можете использовать номер листа, чтобы удалить его. Вот код.
Удалить ActiveSheet
Чтобы удалить активный лист, вы можете использовать объект «ActiveSheet» вместо использования имени листа для указания листа.
Как я уже сказал, он удаляет активный лист, и вы также можете активировать лист перед его удалением. Но вам не обязательно этого делать, так как вы можете обратиться к листу и удалить его, как мы видели в начале этого руководства.
Проверить, существует ли лист перед удалением
Вы также можете написать код таким образом, чтобы он проверял, существует лист или нет, а затем удалял его.
В этом коде у вас есть FOR EACH для перебора всех рабочих листов. А затем IF STATEMENT для удаления листа, если его имя совпадает с именем, которое вы ввели в поле ввода.
Удалить все листы в книге
Я уверен, что у вас есть этот вопрос, но я боюсь, что невозможно удалить все рабочие листы, которые у вас есть в книге. У вас должен остаться хотя бы один рабочий лист.
Но я нашел решение этой проблемы. Вы можете вставить новый пустой лист, а затем удалить все уже существующие.
Вот код: этот код добавляет новый лист и удаляет все остальные листы.
У меня есть большой лист, из которого мне нужно удалить все содержимое. Когда я пытаюсь просто очистить его без VBA, он переходит в режим «не отвечает». При использовании макроса, такого как:
Он тоже не отвечает. Как это сделать быстрее всего?
4 ответа 4
Диапазон .Cells не ограничивается используемыми, поэтому ваш код очищает содержимое 1 048 576 строк и 16 384 столбцов — всего 17 179 869 184 ячеек. Это займет некоторое время. Вместо этого просто очистите UsedRange:
Кроме того, вы можете удалить лист и снова добавить его:
EDIT: (@tavnab и @Azura)
Внимание будущим читателям: вы не можете удалить лист, если он последний/единственный в книге.
В этом случае вы можете сначала добавить новый пустой лист, удалить старый и, наконец, переименовать этот новый лист в имя старого листа. Также обратите внимание, что удаление листа создаст конфликты с формулами в других листах, которые ссылались на недавно удаленный лист, повторное создание листа может не решить эту проблему.Я тоже не уверен, что это сработает. Я пытался сделать это из VBA и просто выделить фрагмент (200 строк, 5000 столбцов) и удалить, и это заморозило его. Затем я вставил цикл и просто попытался перебрать 1000 строк, но это тоже не сработало. Я могу просто зациклить его вручную и зажать F8.
@hc91 - Интересно. Похоже, что данных достаточно, чтобы Excel вызывал проблему, а не VBA. Вы всегда можете удалить рабочий лист и добавить его снова. См. правку.
Внимание будущим читателям: вы не можете удалить лист, если он последний/единственный в книге. В этом случае вы можете сначала добавить новый пустой лист, удалить старый и, наконец, переименовать этот новый лист в имя старого листа.
Кроме того, обратите внимание, что при удалении листа удаляется весь связанный с ним код VBA, если это является проблемой. Я просто удалял и воссоздавал листы в разрабатываемой мной утилите VBA, что загнало меня в угол, когда я начал добавлять события в эти листы. Я рекомендую Sheets("Zeroes").Cells.Delete xlShiftUp, что мне кажется мгновенным.
Также обратите внимание, что удаление листа создаст конфликты с формулами в других листах, которые ссылались на недавно удаленный лист, повторное создание листа может не решить эту проблему.
Технически и исходя из принятого Коминтерном обходного пути, я считаю, что вы действительно хотите удалить все ячейки на листе. Что удаляет форматирование (см. сноску для исключений) и т. д., а также содержимое ячеек. т.е. Листы("Нули").Ячейки.Удалить
В сочетании с пропусками UseRange, ScreenUpdating и Calculation это должно происходить почти мгновенно:
Или, если вы предпочитаете учитывать текущее состояние вычислений Excel:
Сноска: если форматирование было применено ко целому столбцу, оно не удаляется. Это включает в себя цвет шрифта, цвет заливки и границы, категорию формата (например, «Общие», «Дата», «Текст» и т. д.) и, возможно, другие свойства, но
Условное форматирование УДАЛЕНО, как и форматирование Вся строка.
(Форматирование всего столбца весьма полезно, если вы повторно импортируете необработанные данные на лист, поскольку они будут соответствовать первоначально примененным форматам, если выполняется простой импорт типа «Только вставка значений».)
Читайте также: