Vba excel как удалить
Обновлено: 07.07.2024
Использование макросов VBA в Excel может значительно сэкономить время. Вы можете автоматизировать множество повторяющихся задач и создавать новые функции и возможности в Excel с помощью простых кодов макросов VBA.
Но в некоторых случаях вам может потребоваться удалить все макросы из книги Excel (или удалить только определенные макросы).
Это может быть в том случае, когда вы получаете книгу от кого-то еще и хотите освободить ее от макросов, или когда вы отправляете кому-то файл с макросами, а квитанция не нуждается в них в книге.
В этом руководстве я покажу вам несколько действительно простых способов удаления макросов из книги в Microsoft Excel .
Удалите все макросы, сохранив файл в формате XLSX
По умолчанию у вас не может быть кода макроса VBA в формате файла XLSX. Если вы это сделаете, он будет автоматически удален при сохранении файла Excel.
В Excel вы можете использовать макросы только в форматах .XLSM, .XLSB и более старых форматах .XLS. Когда вы сохраняете книгу в любом другом формате, макросы сразу теряются.
Предположим, у вас есть файл с именем Example.xlsm (с макросами), ниже приведены шаги по удалению всех макросов из этого файла:
Это оно! Теперь ваш файл не содержит макросов.
Этот метод удобен тем, что удаляет все макросы из текущей книги Excel за один раз. Однако, если вы хотите удалить некоторые макросы и удалить некоторые, этот метод не подойдет вам (см. Тот, который использует диалоговое окно «Макрос» для этого).
Еще одна хорошая особенность этого метода заключается в том, что у вас все еще есть копия исходного файла со всеми макросами (на случай, если она вам понадобится в будущем).
Удалить определенные макросы из диалогового окна «Макрос»
Хотя предыдущий метод удалял все макросы. это позволяет вам выбрать те, которые вы хотите удалить.
И если вы хотите удалить все макросы, вы также можете это сделать.
Предположим, у вас есть файл с именем Example.xlsm, в котором есть несколько макросов.
Ниже приведены шаги по удалению макроса из этой книги:
Если вы хотите удалить несколько (или все) макросов, повторите шаги 4 и 5.
Кроме того, вы также можете щелкнуть вкладку «Представления», щелкнуть раскрывающийся список «Макросы» и затем выбрать опцию «Просмотр макросов». Это также откроет диалоговое окно Macros.
Хотя этот метод отлично работает, он позволяет удалять только макросы, которые хранятся в модуле редактора Visual Basic. Если у вас есть макросы событий (в определенных листах или ThisWorkbook) или макросы в личной книге макросов, их нельзя удалить с помощью этого метода.
Удалите модуль с макросом
Этот метод дает вам максимальный контроль, поскольку вы можете получить доступ ко всем макросам (будь то в модуле, объектах или личной книге макросов).
Ниже приведены шаги по удалению макроса из редактора Visual Basic:
Если у вас есть модуль с кодом, который вы хотите удалить, вы можете щелкнуть правой кнопкой мыши объект модуля, а затем выбрать опцию «Удалить модуль».
Итак, это три способа удаления макросов из книги Microsoft Excel.
1. Откройте рабочую книгу Excel, в которую планируете импортировать модуль. Для импорта модуля в Личную книгу макросов, откройте любую книгу.
2. Откройте редактор VBA сочетанием клавиш Alt+F11 (в этом сочетании используется левая клавиша Alt). Обратите внимание на наличие в окне редактора VBA окна проводника «Project-VBAProject», обычно, расположенного слева. При отсутствии, отобразите его через главное меню, выбрав «View» — «Project Explorer».
3. В окне проводника «Project-VBAProject» найдите строку «VBAProject (ИмяКниги)», где «ИмяКниги» — это имя книги, в которую вы собираетесь импортировать модуль. Если вы желаете импортировать модуль в Личную книгу макросов, строка называется «VBAProject (PERSONAL.XLSB)». Если у вас нет Личной книги макросов — создайте ее.
4. У выбранной строки раскройте все крестики слева.
5. Откройте контекстное меню, кликнув правой кнопкой мыши на строке «ЭтаКнига», и выберите в нем пункт «Import File…».
6. В открывшемся окне выбора файла найдите импортируемый модуль с расширением .bas и дважды кликните по нему, или кликните один раз и нажмите кнопку «Открыть». Модуль импортирован и отобразился в проводнике под именем «Module» с очередным номером, независимо от имени импортируемого файла.
7. Если вы импортировали модуль в Книгу Excel 2007-2016 с расширением .xlsx, ее необходимо будет пересохранить как «Книга Excel с поддержкой макросов (.xlsm)», иначе импортированный модуль не сохранится.
Инструкцию с картинками вы можете посмотреть здесь в параграфе «Пользовательская функция «СуммаПрописью».
Экспорт модуля
1. Откройте рабочую книгу Excel, из которой вы планируете экспортировать модуль. Для экспорта модуля из Личной книги макросов, откройте любую книгу.
2. Откройте редактор VBA сочетанием клавиш Alt+F11 и в окне проводника «Project-VBAProject» найдите экспортируемый модуль.
3. Откройте контекстное меню, кликнув правой кнопкой мыши на экспортируемом модуле, и выберите в нем пункт «Export File…».
4. В открывшемся окне выберите папку, куда следует сохранить экспортируемый модуль, если необходимо, измените название сохраняемого файла, и нажмите кнопку «Сохранить». Модуль экспортирован и отобразился в выбранном каталоге с указанным именем и расширением .bas.
Удаление модуля
1. В окне проводника «Project-VBAProject» найдите удаляемый модуль.
2. Откройте контекстное меню, кликнув правой кнопкой мыши на удаляемом модуле, и выберите в нем пункт «Remove Module…» с номером удаляемого модуля.
3. VBA Excel предложит экспортировать модуль перед удалением, нажмите «Да», если хотите сохранить модуль, или «Нет», чтобы удалить без сохранения.
При работе с Excel вы проводите большую часть своего времени на листе (который представляет собой область, в которой есть все ячейки и все, что там происходит). Эффективная работа с Excel также означает, что вам придется работать с несколькими листами в одной книге. Excel позволяет легко добавлять несколько листов в книгу, и в то же время вы также можете легко удалить эти листы.
В этом руководстве по Excel я покажу вам, как быстро удалить листы в Excel . Я бы рассмотрел несколько методов, таких как использование сочетаний клавиш, параметры на ленте и VBA для удаления листов в Эксель.
Примечание. В этом уроке я буду использовать слова «лист» и «рабочий лист» как синонимы. Но чтобы сказать вам разницу, листы будут включать в себя рабочие листы, а также листы диаграмм, но поскольку листы диаграмм Excel не так широко используются, всякий раз, когда я использую слово лист, рассматривайте его как лист.
Удаление листов с помощью параметров правой кнопки мыши
Предположим, у вас есть рабочий лист с тремя листами, как показано ниже, и вы хотите удалить Sheet1.
Ниже приведены шаги для этого:
Вы также можете использовать описанную выше технику для листов, которые даже не являются активными. Например, если я сейчас нахожусь на Sheet1 и хочу удалить Shee3, я могу просто щелкнуть правой кнопкой мыши Sheet3 и удалить его.
Вы также можете использовать ту же технику для одновременного удаления нескольких листов .
Например, если я хочу удалить Sheet2 и Sheet3 за один раз, я могу удерживать контрольную клавишу и нажимать на Sheet2 и Sheet3 один за другим (при этом все еще удерживая контрольную клавишу).
Удерживая контрольную клавишу, Excel позволит мне выбрать сразу несколько листов. Когда я закончу выбирать нужные листы, я могу оставить контрольную клавишу. теперь я могу щелкнуть правой кнопкой мыши любую из выбранных вкладок листов и нажать «Удалить».
Помните, что вы не можете удалить все листы в книге в Excel. в книге всегда должен быть хотя бы один лист. если вы попытаетесь удалить все рабочие листы (или единственный активный лист), вы увидите подсказку, которая сообщит вам, что вам не разрешено это делать
Сочетания клавиш для удаления рабочих листов
Существует не одна комбинация клавиш, а две с половиной, которые можно использовать для удаления листов в Excel.
Я говорю «два с половиной», потому что в одном из сочетаний клавиш он использует мышь и клавиатуру (и это все еще более быстрый способ сделать это).
Гибридная комбинация клавиш для удаления листа
Чтобы удалить выбранный рабочий лист или рабочие листы, щелкните правой кнопкой мыши, а затем нажмите клавишу D на клавиатуре.
Лично я считаю, что это намного быстрее, чем просто удаление листа с помощью мыши (как я уже говорил в предыдущем разделе).
Обычное сочетание клавиш для удаления листа
Если вы предпочитаете отказаться от мыши и использовать только клавиатуру, указанное выше сочетание клавиш удалит активный лист или выбранные листы.
Вам нужно нажимать эти клавиши последовательно (т. Е. Одну за другой).
Хотя это может выглядеть как немного более длинное сочетание клавиш, как только вы привыкнете к нему, оно будет таким же быстрым, как и любой другой метод, описанный в этом руководстве.
Устаревшее сочетание клавиш для удаления листов
Как и у всех остальных, у Excel тоже есть прошлое, и оно не такое уж красивое. Я говорю об эпохе до-ленточного стиля.
По соображениям совместимости Excel по-прежнему позволяет некоторым из этих старых сочетаний клавиш работать в более новых версиях. и во многих случаях эти ранние ярлыки короче и лучше.
К счастью, есть устаревшее сочетание клавиш, которое работает для удаления листов в Excel.
Удаление ActiveSheet с помощью VBA
Когда дело доходит до удаления одного листа или нескольких рабочих листов, лучше использовать вышеупомянутые методы.
Хотя VBA может автоматизировать процесс, он полезен, когда вам нужно повторить задачу несколько раз.
Как вы увидите, с помощью VBA вы можете сделать гораздо больше, когда дело доходит до удаления листов в Excel.
Итак, я расскажу вам о более сложных случаях использования, но перед этим давайте посмотрим, как просто удалить активный лист с помощью VBA.
Ниже приведен код VBA, который удалит активный лист:
Если вы используете его в непосредственном окне, вы можете просто использовать следующую строку:
Когда вы используете приведенный выше код для удаления активного листа, Excel покажет вам приглашение, в котором вам нужно будет нажать кнопку удаления, чтобы подтвердить действие.
Удаление листа без запроса подтверждения
Итак, вот код VBA, который гарантирует, что листы удалены, но вы не видите окно с запросом подтверждения.
В приведенном выше коде я установил для свойства Application.DisplayAlerts значение false, что означает, что Excel не будет показывать вам никаких предупреждений на дисплее во время выполнения кода.
Также очень важно убедиться, что вы вернули его к истине в конце кода, чтобы восстановить функциональность (как вы можете видеть, что я сделал в приведенном выше коде).
Внимание! Если для свойства Application.DisplayAlerts установлено значение false, Excel просто удалит рабочий лист, и восстановить его будет невозможно. поэтому я советую вам сделать резервную копию, прежде чем использовать такой код.
Удаление листа по имени (если он существует) с помощью VBA
VBA позволяет автоматизировать процесс удаления определенного листа (на нескольких листах) на основе имени листа.
Например, если у вас есть рабочий лист с названием «Продажи», вы можете использовать приведенный ниже код, чтобы удалить его:
Этот код удалит только лист с именем «Продажи».
Это полезно, когда у вас есть рабочая тетрадь с большим количеством листов, и вы не хотите просматривать все листы, найдите тот, у которого есть имя продажи, и удалите его вручную.
С приведенным выше кодом не имеет значения, сколько листов в книге, он просто удалит рабочий лист.
И поскольку я не менял свойство Application.DisplayAlert, вы увидите приглашение, в котором вам нужно будет нажать кнопку удаления, чтобы подтвердить продолжительность листа продаж.
Если вы хотите удалить несколько листов по их имени, вы также можете это сделать.
Например, приведенный ниже код удалит листы с именами Продажи, Маркетинг, Финансы:
Удаление всех листов, кроме активного листа, с помощью VBA
Если у вас есть книга с несколькими листами, и вы хотите удалить все листы, кроме активного листа, VBA, вероятно, является одним из лучших способов сделать это.
Ниже приведен код VBA, который удалит все листы, кроме активного листа в книге.
Обратите внимание, что я сказал, что свойство Application.DisplayAlerts находится в начале кода, так как я не хочу видеть подсказку для каждого удаляемого листа.
Удалить все листы с определенной текстовой строкой в имени
Это немного более продвинутый вариант эффективного использования VBA при удалении листов.
Предположим, у вас есть книга с множеством разных листов, и вы хотите удалить все листы, в которых есть определенная текстовая строка, тогда вы можете легко сделать это с помощью VBA.
Например, ниже у меня есть рабочая книга, из которой я хочу удалить все рабочие листы, содержащие текстовую строку «Продажи».
Ниже приведен код VBA, который сделает это:
В приведенном выше коде используется оператор if-then для просмотра всех листов в книге. Он проверяет имя всех этих рабочих листов, и если имя содержит слово «Продажи», то этот рабочий лист удаляется.
Если вы хотите изменить код и найти любую другую текстовую строку, вы можете изменить ее в пятой строке приведенного выше кода.
Также обратите внимание, что я использовал звездочку (*), которая является символом подстановки, с обеих сторон текстовой строки, которую мы ищем в имени рабочего листа. Это гарантирует, что независимо от того, где строка появляется в имени рабочего листа, она все равно будет удалена.
Вы также можете изменить код, чтобы убедиться, что удаляются только те листы, где текстовая строка появляется в начале имени листа.
Например, если вы хотите удалить те листы, в которых термин продажи появляется в начале, используйте следующий код в пятой строке.
Здесь я использовал подстановочный знак только после текста «drink», а не перед ним. Это гарантирует, что при проверке имен рабочего листа только те будут удовлетворять критериям, в которых термин «Продажи» стоит в начале имени.
Итак, это некоторые из методов, которые вы можете использовать, если хотите удалить листы в Excel. В большинстве случаев вы можете легко сделать это прямо на рабочем листе с помощью мыши или сочетания клавиш.
Но если у вас тяжелая книга с большим количеством листов и вы хотите быстро удалить определенные типы листов, вы также можете использовать VBA.
При работе с текстовыми данными в Excel вам может потребоваться удалить текст до или после определенного символа или текстовой строки.
Например, если у вас есть данные об именах и обозначениях людей, вы можете удалить обозначение после запятой и оставить только имя (или наоборот, если вы сохраните обозначение и удалите имя).
Иногда это можно сделать с помощью простой формулы или быстрого поиска и замены, а иногда требуются более сложные формулы или обходные пути.
В этом уроке я покажу вам, как удалить текст до или после определенного символа в Excel (на разных примерах).
Итак, давайте начнем с нескольких простых примеров.
Удаление текста после символа с помощью функции поиска и замены
Если вы хотите быстро удалить весь текст после определенной текстовой строки (или перед текстовой строкой), вы можете сделать это с помощью «Найти и заменить» и подстановочных знаков.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите удалить обозначение после символа запятой и оставить текст перед запятой.
Ниже приведены шаги для этого:
Вышеупомянутые шаги позволят найти запятую в наборе данных и удалить весь текст после запятой (включая запятую).
Поскольку это заменяет текст из выбранных ячеек, рекомендуется скопировать текст в другой столбец, а затем выполнить эту операцию поиска и замены или создать резервную копию ваших данных, чтобы у вас были исходные данные без изменений.
Как это работает?
Когда я использую его после запятой (в поле «Найти»), а затем нажимаю кнопку «Заменить все», он находит первую запятую в ячейке и считает ее совпадением.
Это связано с тем, что знак звездочки (*) считается соответствием всей текстовой строке, следующей за запятой.
Поэтому, когда вы нажимаете кнопку «Заменить все», она заменяет запятую и весь последующий текст.
Примечание . Этот метод работает хорошо, если в каждой ячейке есть только одна запятая (как в нашем примере). Если у вас несколько запятых, этот метод всегда найдет первую запятую, а затем удалит все после нее. Таким образом, вы не можете использовать этот метод, если хотите заменить весь текст после второй запятой и оставить первый как есть.
Если вы хотите удалить все символы перед запятой, измените запись в поле поиска, поставив перед запятой знак звездочки (* вместо *)
Удалить текст с помощью формул
Если вам нужно больше контроля над поиском и заменой текста до или после определенного символа, лучше использовать встроенные текстовые формулы в Excel.
Предположим, у вас есть приведенный ниже набор данных, из которого вы хотите удалить весь текст после запятой.
Ниже приведена формула для этого:
В приведенной выше формуле используется функция FIND (НАЙТИ), чтобы найти положение запятой в ячейке.
Этот номер позиции затем используется функцией LEFT для извлечения всех символов перед запятой. Поскольку мне не нужна запятая как часть результата, я вычел 1 из результирующего значения формулы поиска.
Это был простой сценарий.
Возьмем немного сложный.
Предположим, у меня есть этот набор данных ниже, из которого я хочу удалить весь текст после второй запятой.
Вот формула, которая сделает это:
Поскольку в этом наборе данных есть несколько запятых, я не могу использовать функцию FIND, чтобы получить позицию первой запятой и извлечь все, что находится слева от нее.
Мне нужно как-то узнать положение второй запятой, а затем извлечь все, что находится слева от второй запятой.
Для этого я использовал функцию SUBSTITUTE, чтобы заменить вторую запятую восклицательным знаком. Теперь это дает мне уникальный персонаж в камере. Теперь я могу использовать положение восклицательного знака, чтобы извлечь все, что находится слева от второй запятой.
Эта позиция восклицательного знака используется в функции LEFT для извлечения всего, что находится до второй запятой.
Все идет нормально!
Но что, если в наборе данных непоследовательное количество запятых.
В этом случае мне нужно как-то определить позицию последнего вхождения запятой, а затем извлечь все, что находится слева от нее.
Ниже приведена формула, которая сделает это
В приведенной выше формуле функция LEN используется для определения общей длины текста в ячейке, а также длины текста без запятой.
Когда я вычитаю эти два значения, получается общее количество запятых в ячейке.
Таким образом, это дало бы мне 3 для ячейки A2 и 2 для ячейки A4.
Это значение затем используется в формуле SUBSTITUTE для замены последней запятой восклицательным знаком. И затем вы можете использовать левую функцию для извлечения всего, что находится слева от восклицательного знака (где раньше была последняя запятая)
Как вы можете видеть в примерах, использование комбинации текстовых формул позволяет обрабатывать множество различных ситуаций.
Кроме того, поскольку результат связан с исходными данными, при изменении исходных данных результат будет автоматически обновляться.
Удаление текста с помощью заливки Flash
Он работает, выявляя закономерности, когда вы вручную вводите данные, а затем экстраполируете их, чтобы получить данные для всего столбца.
Поэтому, если вы хотите удалить текст до или после определенного символа, вам просто нужно показать flash fairy, как будет выглядеть результат (введя его вручную пару раз), и flash fill автоматически распознает узор и даст вам результаты.
Позвольте показать вам это на примере.
Ниже у меня есть набор данных, из которого я хочу удалить весь текст после запятой.
Вот шаги, чтобы сделать это с помощью Flash Fill:
Вышеупомянутые шаги дадут вам результат, как показано ниже:
Вы также можете использовать сочетание клавиш Control + E для Flash Fill после выбора ячеек в столбце результатов (столбец B в нашем примере)
Так что не забудьте еще раз проверить результаты Flash Fill.
И точно так же, как мы удалили весь текст после определенного символа с помощью флэш-заливки, вы можете использовать те же шаги, чтобы удалить текст перед определенным символом. просто покажите вручную в соседнем столбце flash fill, как результат должен выглядеть как мой Интернет, и он сделает все остальное.
Удалить текст с помощью VBA (пользовательская функция)
Вся концепция удаления текста до или после определенного символа зависит от нахождения позиции этого символа.
Как показано выше, найти последнее вхождение этого символа хорошим означает использовать смесь формул.
Если это то, что вам нужно делать довольно часто, вы можете упростить этот процесс, создав настраиваемую функцию с помощью VBA (называемую функциями, определяемыми пользователем).
После создания вы можете повторно использовать эту функцию снова и снова. Это также намного проще и проще в использовании (поскольку большая часть тяжелой работы выполняется кодом VBA в серверной части).
Ниже кода VBA, который можно использовать для создания настраиваемой функции в Excel:
For i = rLen To 1 Step -1
Вам необходимо поместить код VBA в обычный модуль редактора VB или в личную книгу макросов. Когда он у вас есть, вы можете использовать его как любую другую обычную функцию рабочего листа в книге.
Эта функция принимает 2 аргумента:
- Ссылка на ячейку, для которой вы хотите найти последнее вхождение определенного символа или текстовой строки.
- Символьная или текстовая строка, позицию которой вам нужно найти
Предположим, теперь у вас есть приведенный ниже набор данных, и вы хотите удалить весь текст после последней запятой и иметь только текст до последней запятой.
Ниже приведена формула, которая сделает это:
В приведенной выше формуле я указал, чтобы найти позицию последней запятой. Если вы хотите найти положение какого-либо другого символа или текстовой строки, вы должны использовать это как второй аргумент в функции.
Как видите, это намного короче и проще в использовании по сравнению с формулой с длинным текстом, которую мы использовали в предыдущем разделе.
Если вы поместите код VBA в модуль в книге, вы сможете использовать эту настраиваемую функцию только в этой конкретной книге. Если вы хотите использовать это во всех книгах в вашей системе, вам необходимо скопировать и вставить этот код в личную книгу макросов.
Вот некоторые из писем, которые вы можете использовать для быстрого удаления текста до или после определенного символа в Excel.
Если это простая одноразовая вещь, вы можете сделать это с помощью функции поиска и замены. что, если это немного сложнее, тогда вам нужно использовать комбинацию встроенных формул Excel или даже создать свою собственную формулу с помощью VBA.
Читайте также: