Удалить структуру в excel vba

Обновлено: 30.06.2024

Примеры

Пример 4
Удаление одного файла с проверкой его существования:

Пример 5
Удаление нескольких файлов по шаблону:

В результате работы этого кода VBA Excel из папки «Новая папка» будут удалены все файлы с расширением .docx .

Содержание рубрики VBA Excel по тематическим разделам со ссылками на все статьи.

Как очистить все в выделенном диапазоне ячеек?

Способ

Если лист содержит очень преочень много данных, тогда чтобы очистить лист Excel самым быстрым способом будет удалить его и создать заново.

Предложенных 3 вариантов удаления всех данных на листе Excel я думаю будет достаточно для решения повседневных задач на VBA.

Методы очистки ячеек

Метод	Очищаемые свойства	Примечание
Range.Clear	Почти все свойства	Ширина и высота ячеек не изменяются
Range.ClearComments	Комментарии	Для Excel в составе Office 365
Range.ClearContents	Формулы и значения	Исходное форматирование сохраняется
Range.ClearFormats	Свойства, задающие форматы	В том числе отмена объединения ячеек
Range.ClearHyperlinks	Гиперссылки	Текст и форматирование сохраняются
Range.ClearNotes	Примечания и заметки	Примечания – для локальных программ Excel, заметки – для Excel в составе Office 365
Range.ClearOutline	Структура данных

Range – выражение, возвращающее диапазон ячеек.

Отключение активной надстройки

Перед тем как удалить надстройку Excel xla/xlam, необходимо сделать ее неактивной, то есть отключить. Чтобы узнать состояние надстройки (активная она или нет) необходимо нажать кнопку «Office» и перейти в «Параметры Excel». Появляется окно «Параметры Excel», где в разделе «Надстройки» виден список всех установленных надстроек, сгруппированных на активные и неактивные. Активные – это те надстройки, которые на данный момент подключены, соответственно неактивные – это те, которые в данный момент отключены. Здесь также можно узнать место расположения файла удаляемой надстройки (оно нам вскоре понадобится). Обратите внимание на скриншот ниже, на нем видны активные надстройки, одну из них с названием test.xla мы и будем удалять.

Удалить & Заменить лист

Кроме того, вызов подпункта ниже приведет к удалению и замене листа, присвоив ему то же имя и поместив его в ту же позицию вкладки, что и оригинал:

Безопасное удаление листа

Если однострочник (выше) не работает для вас (возможно, из-за некоторых упрямых цветов фона/изображений), то здесь подпункт, который вы можете вызвать, чтобы удалить любой указанный лист:

Назовите это просто как:

… где asdf — это имя листа (вкладки), который нужно удалить.

Полное удаление программы

Если Вы больше не планируете пользоваться программой, то помимо отключения надстройки необходимо удалить сам файл с программой. Для этого:

Откройте диалоговое окно Надстройки повторив действия указанные выше.
Выделите надстройку VBA-Excel и нажмите Обзор.
Откроется местоположение файла. Удалите его и программа удалится полностью.

Синтаксис

PathName – это строковое выражение, задающее одно или несколько имен файлов (по шаблону), которые требуется удалить.

Строка PathName может содержать каталоги (папки) и букву диска. Если файл с именем PathName не существует, будет сгенерирована ошибка.

Оператор Kill поддерживает использование знаков подстановки в последнем компоненте параметра PathName (собственное имя файла без пути к нему):

Звездочка (*) – заменяет любое количество символов или ни одного.
Вопросительный знак (?) – заменяет один символ или ни одного.

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

Как очистить ячейки средствами VBA?

Для более гибкой очистки ячеек создана надстройка, позволяющая очистить диапазон по выбранным параметрам . Диалоговое окно этой надстройки можно увидеть на скриншоте ниже. С ее помощью пользователи могут выбрать диапазон для очистки, а также выбрать параметр, либо сочетание параметров, от которых будет очищен выбранный диапазон.

Использование надстройки позволяет:

1. Одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов Excel;

2. на выбор пользователя удалять значения ячеек, примечания, гиперссылки, форматы, шрифты, заливки, рамки и объединения ячеек;

3. выбирать по своему усмотрению один из четырех режимов обработки данных (смотри скриншот).

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

Надстройка легко устанавливается и запускается одним нажатием кнопки, выведенной прямо в главное меню Excel.

Как очистить только форматы в выбранном диапазоне ячеек?

Удаление файла надстройки

Итак, из приведенного выше окна мы узнали текущее состояние надстройки, которую собираемся удалить. Надстройка test.xla активная, поэтому переходим в поле «Управление», выбираем из списка «Надстройки Excel» и нажимаем кнопку «Перейти». Снимаем флажок перед названием надстройки.

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

В общем случае структура — это такой режим работы, который позволяет разбить данные на определенные уровни детализации, а также просматривать только заголовки и подзаголовки документа. Структура наиболее полезна при создании итоговых отчетов, где не требуется чрезмерная детализация. Структуру невозможно создавать на основе произвольной таблицы данных. Ее построение основывается на таблицах с иерархической структурой данных. Объект Outline (Структура) по своей иерархии вкладывается в семейство рабочих листов Worksheets . Свойство Outline рабочего листа возвращает объект Outline . Вручную на рабочем листе структура создается с помощью команды Данные | Группа и структура | Создание структуры (Data | Group and Outline | Auto Outline) .

Приведем основные свойства объекта Outline .

Таблица 1. Свойства объекта Outline

Свойство	Назначение
AutomaticStyles	Допустимые значения: True (структура строится на основе автоматических стилей) и False (в противном случае)
SummaryColumn	Возвращает местоположение итоговых столбцов. Допустимые значения: xlLeft (итоговые столбцы располагаются слева от столбцов, по которым подводятся итоги) и xlRight (итоговые столбцы располагаются слева)
SummaryRow	Возвращает местоположение итоговых строк. Допустимые значения: xlAbove (итоговые строки располагаются выше строк, по которым подводятся итоги) и xlBelow (итоговые строки располагаются ниже)

Перечислим наиболее часто используемые методы объекта Outline .

Таблица 2. Методы объекта Outline Метод Назначение ShowLevels Отображает указанное число уровней структуры по строкам и столбцам. Синтаксис:

RowLevels - устанавливает число отображаемых уровней структуры по строкам;
ColumnLevels - устанавливает число отображаемых уровней структуры по столбцам.

Приведем соответствие между построением структуры вручную и аргументами методов и свойств объекта Outline на примере организации отчета о продаже в виде структуры с двумя уровнями по столбцам и строкам (рисунок 1).

Рис.1. Отчет о продажах

Рис.2. Диалоговое окно Структура документа

Рис.3. Структурированная таблица с детализацией

При выводе структуры по левому и верхнему краю рабочего листа отображаются кнопки, помеченные символами "+" и "-" , которые управляют отображением и скрытием уровней детализации структуры, а также номеров уровней. Щелчок на кнопке со знаком плюс приводит к отображению детализации, а на кнопке со знаком минус — к скрытию детализации. Щелчком на кнопке с номером уровня детализации также можно управлять отображением и скрытием всего уровня.Нажмите кнопки со знаком минус — с тем, чтобы скрыть детализацию отчетной таблицы (рисунок 4).

На этом шаге мы рассмотрим назначение, основные свойства и методы этого элемента .

Элемент управления ListBox (Список) создается с помощью кнопки Список (ListBox) (рисунок 1).

Рис.1. Список в форме

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

Приведем наиболее часто используемые свойства элемента управления ListBox .

Таблица 1. Основные свойства ListBox Свойство Назначение ListIndex Возвращает номер текущего элемента списка. Нумерация элементов списка начинается с нуля ListCount Возвращает число элементов списка TopIndex Возвращает элемент списка с наибольшим номером ColumnCount Устанавливает число столбцов в списке TextColumn Устанавливает столбец в списке, элемент которого возвращается свойством Text Enabled Допустимые значения: True (запрещен выбор значения из списка пользователем) и False (в противном случае) Text Возвращает выбранный в списке элемент List Возвращает элемент списка, стоящий на пересечении указанных строки и столбца. Синтаксис:

fmMultiSelectSingle (выбор только одного элемента);
fmMultiSelectMulti (разрешен выбор нескольких элементов посредством либо щелчка, либо нажатием клавиши Пробел );
fmMultiSelectExtended (разрешено использование клавиши Shift при выборе ряда последовательных элементов списка).

String — строка, устанавливающая ширину столбцов. В следующем примере устанавливается ширина каждого из трех столбцов списка:

fmListStylePlain (выбранный элемент из списка выделяется цветом);
fmListStyleOption (перед каждым элементом в списке располагается флажок и выбор элемента из списка соответствует установке этого флажка).

fmMatchEntryNone (режим вывода подходящего элемента в списке отключен);
fmMatchEntryFirstLetter (вводит подходящий элемент по набранной первой букве. В этом случае, предпочтительно, чтобы элементы списка были бы упорядочены в алфавитном порядке)
fmMatchEntryComplete (вводит подходящий элемент по полному набранному имени).

если свойство BoundColumn равно 0, то свойство Value возвращает индекс выбранной строки, т.е. в этом случае оно действует как свойство ListIndex ;
если свойство BoundColumn принимает значение из диапазона от 1 до количества столбцов в списке, то свойство Value возвращает элемент из выбранной строки, стоящий в столбце, определенном свойством BoundColumn .

Приведем наиболее часто используемые методы элемента управления ListBox .

Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:

Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.

Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,

скрывать подробности по месяцам, оставляя только кварталы
скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.

В реальной жизни примеров таких таблиц - море.

Способ 1. Скрытие строк и столбцов

Способ, прямо скажем, примитивный и не очень удобный, но два слова про него сказать можно. Любые выделенные предварительно строки или столбцы на листе можно скрыть, щелкнув по заголовку столбца или строки правой кнопкой мыши и выбрав в контекстном меню команду Скрыть (Hide) :

Для обратного отображения нужно выделить соседние строки/столбцы и, щелкнув правой кнопкой мыши, выбрать в меню, соответственно, Отобразить (Unhide) .

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

Способ 2. Группировка

Если выделить несколько строк или столбцов, а затем выбрать в меню Данные - Группа и структура - Группировать (Data - Group and Outline - Group) , то они будут охвачены прямоугольной скобкой (сгруппированы). Причем группы можно делать вложенными одна в другую (разрешается до 8 уровней вложенности):

Более удобный и быстрый способ - использовать для группировки выделенных предварительно строк или столбцов сочетание клавиш Alt+Shift+стрелка вправо, а для разгруппировки Alt+Shift+стрелка влево, соответственно.

Такой способ скрытия ненужных данных гораздо удобнее - можно нажимать либо на кнопку со знаком "+" или "-", либо на кнопки с цифровым обозначением уровня группировки в левом верхнем углу листа - тогда все группы нужного уровня будут сворачиваться или разворачиваться сразу.

Кроме того, если в вашей таблице присутствуют итоговые строки или столбцы с функцией суммирования соседних ячеек, то есть шанс (не 100%-ый правда), что Excel сам создаст все нужные группировки в таблице одним движением - через меню Данные - Группа и структура - Создать структуру (Data - Group and Outline - Create Outline) . К сожалению, подобная функция работает весьма непредсказуемо и на сложных таблицах порой делает совершенную ерунду. Но попробовать можно.

В Excel 2007 и новее все эти радости находятся на вкладке Данные (Data) в группе Структура (Outline) :

Способ 3. Скрытие помеченных строк/столбцов макросом

Этот способ, пожалуй, можно назвать самым универсальным. Добавим пустую строку и пустой столбец в начало нашего листа и отметим любым значком те строки и столбцы, которые мы хотим скрывать:

Теперь откроем редактор Visual Basic (ALT+F11), вставим в нашу книгу новый пустой модуль (меню Insert - Module) и скопируем туда текст двух простых макросов:

Как легко догадаться, макрос Hide скрывает, а макрос Show - отображает обратно помеченные строки и столбцы. При желании, макросам можно назначить горячие клавиши (Alt+F8 и кнопка Параметры), либо создать прямо на листе кнопки для их запуска с вкладки Разработчик - Вставить - Кнопка (Developer - Insert - Button) .

Способ 4. Скрытие строк/столбцов с заданным цветом

Допустим, что в приведенном выше примере мы, наоборот, хотим скрыть итоги, т.е. фиолетовые и черные строки и желтые и зеленые столбцы. Тогда наш предыдущий макрос придется немного видоизменить, добавив вместо проверки на наличие "х" проверку на совпадение цвета заливки с произвольно выбранными ячейками-образцами:

Однако надо не забывать про один нюанс: этот макрос работает только в том случае, если ячейки исходной таблицы заливались цветом вручную, а не с помощью условного форматирования (это ограничение свойства Interior.Color). Так, например, если вы с помощью условного форматирования автоматически подсветили в своей таблице все сделки, где количество меньше 10:

. и хотите их скрывать одним движением, то предыдущий макрос придется "допилить". Если у вас Excel 2010-2013, то можно выкрутиться, используя вместо свойства Interior свойство DisplayFormat.Interior, которое выдает цвет ячейки вне зависимости от способа, которым он был задан. Макрос для скрытия синих строк тогда может выглядеть так:

Ячейка G2 берется в качестве образца для сравнения цвета. К сожалению, свойство DisplayFormat появилось в Excel только начиная с 2010 версии, поэтому если у вас Excel 2007 или старше, то придется придумывать другие способы.

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