Vba excel удалить столбец
Обновлено: 16.07.2024
Предположим, что у нас имеется вот такая таблица, с которой приходится "танцевать" каждый день:
Кому таблица покажется маленькой - мысленно умножьте ее по площади в двадцать раз, добавив еще пару кварталов и два десятка крупных российских городов.
Задача - временно убирать с экрана ненужные в данный момент для работы строки и столбцы, т.е.,
- скрывать подробности по месяцам, оставляя только кварталы
- скрывать итоги по месяцам и по кварталам, оставляя только итог за полугодие
- скрывать ненужные в данный момент города (я работаю в Москве - зачем мне видеть Питер?) и т.д.
В реальной жизни примеров таких таблиц - море.
Способ 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 или старше, то придется придумывать другие способы.
Копирование, вставка, вырезание, удаление, вставка - это некоторые из общих операций, используемых для выполнения в Excel. Самый простой способ сделать это, используя сочетания клавиш или встроенные функции. Но когда вы хотите выполнить операцию в один клик или автоматически, VBA является решением. Мы можем автоматизировать эти задачи, используя макросы Excel. VBA также предоставляет различные методы, аналогичные функциям Excel. Это выполняет эти задачи плавно в VBA.
Метод удаления столбцов используется для удаления одного или нескольких столбцов в Excel VBA. Свойство delete для столбцов используется вместе с индексом столбца.
Синтаксис для удаления столбца в Excel VBA
Синтаксис для удаления столбца в Excel, как показано ниже.
- Где ссылка на столбец - это номер столбца, который вы хотите удалить.
- Столбцы ((RowIndex), ColumnIndex)) здесь диапазон столбцов также принимается.
Как удалить столбец в Excel с помощью VBA?
Мы узнаем, как удалить столбец в VBA, с несколькими примерами в Excel.
Вы можете скачать этот шаблон Excel VBA Удалить столбец здесь - VBA Удалить шаблон Excel столбца
VBA Удалить столбец - Пример № 1
Из базы данных посещаемости указывается время отъезда в офис для некоторых сотрудников. Это данные, собранные за одну неделю, с понедельника по пятницу.
Мы хотим удалить столбец пятница.
Выполните следующие шаги, чтобы удалить столбец в Excel.
Шаг 1: Итак, сначала мы можем создать простую функцию как delete (), поскольку delete - это ключевое слово, которое не является предпочтительным.
Код:
Шаг 2: Теперь давайте использовать свойство columns.
Код:
В столбцах () упоминается «6», поскольку указанный столбец является 6- м столбцом в таблице.
Шаг 3: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
Если вы проверите таблицу, она будет выглядеть так, как показано ниже. Где столбец пятница был удален.
VBA Удалить столбец - Пример № 2
Другой простой способ выполнить удаление - указать ссылку на столбец в алфавитном порядке.
Выполните следующие шаги, чтобы удалить столбец в Excel с помощью VBA.
Шаг 1: Поскольку пятница - это столбец F, укажите адрес столбца, как показано ниже в коде.
Код:
При использовании алфавита не забудьте поставить алфавит в двойных кавычках.
Шаг 2: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
Это удалит столбец пятница из таблицы.
VBA Удалить столбец - Пример № 3
В той же таблице, если вы хотите удалить более одного столбца, следует указать диапазон столбцов.
Выполните следующие шаги, чтобы удалить последние два столбца в Excel.
Шаг 1: Код можно изменить, как показано ниже.
Код:
Диапазон указан как «E: F», и это приведет к удалению столбца от F до G.
Шаг 2: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
Последние два столбца удалены.
VBA Удалить столбец - Пример № 4
Посмотрим, что будет, если мы удалим средние столбцы в таблице.
Выполните следующие шаги, чтобы удалить средние столбцы в таблице.
Шаг 1: Код можно изменить, как показано ниже.
Код:
Здесь «B: C» относится к колонке понедельник и вторник.
Шаг 2: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
После запуска кода, если вы проверите таблицу, вы можете увидеть столбец после смещения «B: C» справа налево.
VBA Удалить столбец - Пример № 5
В приведенном выше примере мы не упомянули имя рабочего листа. Если рабочая книга содержит более одного листа, то следует указать имя рабочего листа, тогда только код выполнит правильное действие.
На двух листах содержатся сведения о сотрудниках за два месяца, январь и февраль. Поскольку на одном листе имеется более одного листа, чтобы избежать ошибок, лучше указать имя листа.
Выполните следующие шаги, чтобы удалить столбец в Excel с помощью VBA.
Шаг 1: лист должен быть выбран с помощью кода.
Код:
Шаг 2: Теперь нужно указать код для удаления столбца.
Код:
Шаг 3: Запустите этот код, нажав F5 или кнопку Run, и посмотрите результат.
Будет выбран лист «Jan», а столбцы B, C, т.е. понедельник, вторник, будут удалены из таблицы.
Удаление нескольких столбцов с использованием объекта диапазона в VBA
Объект диапазона также используется для удаления столбца в VBA вместо удаления столбца. Если используется объект диапазона, код будет выглядеть так, как показано ниже.
Диапазон («B: C») представляет собой диапазон столбцов, который необходимо удалить.
Удаление одного столбца с использованием объекта диапазона
Чтобы удалить один столбец с использованием объекта диапазона, необходимо указать диапазон, как показано ниже.
Диапазон («B: B») указывает на один столбец, и он будет удален.
То, что нужно запомнить
- Столбец может быть указан с помощью номера столбца или соответствующего алфавита при удалении.
- При удалении нескольких столбцов номера не будут приняты в качестве ссылки на столбец.
- Вместо свойства столбцов можно использовать объект Range для удаления столбца в VBA.
Рекомендуемые статьи
Это руководство по удалению столбца VBA. Здесь мы обсудили, как удалить столбец в Excel с помощью VBA, а также с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Скрытие и отображение столбцов в Excel - самая простая задача. И автоматизировать этот процесс даже легко. VBA Hide Column не требует огромного кода для написания. Но прежде чем мы начнем писать код для скрытия столбца в VBA, нам нужно понять несколько вещей и требований. Нам нужно знать имя столбца или последовательность, которую мы хотим скрыть, и диапазон ячейки, которая является необязательной.
Как скрыть столбцы в Excel VBA?
Мы научимся скрывать столбец в VBA с помощью нескольких примеров в Excel.
Вы можете скачать этот шаблон Excel VBA Hide Columns здесь - VBA Скрыть шаблон Excel Columns
Excel VBA Hide Column - Пример № 1
Для этого нам нужны данные в виде нескольких заполненных столбцов. Здесь у нас есть набор данных, в котором мы случайным образом взяли числа от 1 до 40 в разных 4 столбцах A, B, C и D соответственно, и все столбцы окрашены в разные цвета только для лучшего отображения на веб-странице.
Предположим, если мы хотим скрыть столбцы A и B, то сначала нам нужно выбрать оба столбца, как показано ниже.
Теперь щелкните правой кнопкой мыши и выберите « Скрыть», как показано ниже.
Выбранные столбцы будут скрыты после этого. Мы могли видеть только столбцы C и D с данными.
Теперь, если подобная работа может быть выполнена и макросом VBA. Для этого нам понадобится модуль. Перейдите в VBA и выберите модуль из списка меню «Вставка», как показано ниже.
В открывшемся модуле напишите подкатегорию VBA Hide Column, или мы можем выбрать любое другое имя в соответствии с нашим выбором, как показано ниже.
Код:
Сначала выберите столбцы, которые нам нужно скрыть. Здесь мы скрываем столбец C.
Код:
Поскольку мы хотим скрыть весь столбец C, выберите функцию FullColumn, а затем Hidden, как показано ниже.
Код:
Теперь, чтобы это произошло, мы можем выбрать ответ ИСТИНА или ЛОЖЬ. Если мы выберем Hidden как TRUE, тогда это будет скрывающая функция или если FALSE, тогда она будет работать как скрывающая функция. Поскольку мы хотим скрыть выбранные столбцы C из диапазона, мы бы выбрали здесь TRUE.
Код:
Теперь скомпилируйте код и запустите его, нажав кнопку Play, расположенную под строкой меню в VBA. Мы увидим, что столбец C теперь скрыт, теперь он показывает только столбцы A, B и D.
Если мы хотим скрыть более одного столбца вместе. Затем в диапазоне нам нужно выбрать эти столбцы, как показано ниже. Здесь мы выбираем столбцы B и C.
Код:
Теперь снова запустите код.
Здесь мы видим, столбцы B и C теперь скрыты. И только столбцы A и D видны.
Excel VBA Hide Column - Пример № 2
Вместо того, чтобы выбирать столбцы путем упоминания имени, мы также можем скрыть любой столбец по их последовательности. Во многих случаях этот процесс не является предпочтительным, поскольку мы можем легко узнать имя столбца, в котором находится столбец в алфавитном порядке. Этот процесс похож на код, который мы видели в примере № 1, где мы выбрали имя столбца как B и B: C, мы выберем порядковый номер, в котором расположен столбец. Если предположить, что мы хотим скрыть столбец A, используя код примера-1, нам просто нужно поместить порядковый номер, а не выбирать его с помощью Range.
Код:
Теперь запустите этот код, нажав клавишу F5 или нажав кнопку Play. Мы увидим, столбец А, который находится на первой позиции, теперь скрыт.
Excel VBA Hide Column - Пример № 3
Есть еще один способ скрыть столбец, используя код VBA. Это также просто, как код, который мы видели в примере № 1. Для этого мы снова будем использовать те же данные, которые мы использовали в приведенном выше примере. Запишите подкатегорию под любым именем или именем выполняемой функции.
Код:
Выберите любой диапазон столбца, который нам нужно скрыть. Здесь мы выбираем тот же столбец C.
Код:
Чтобы активировать выбор необходимого столбца, выберите функцию « Столбцы», как показано ниже.
Код:
Затем используйте скрытую команду, чтобы активировать функцию скрытия и отображения в VBA.
Код:
И выберите ИСТИНА, чтобы скрыть выбранный столбец, и ЛОЖЬ, чтобы скрыть выбранный скрытый столбец.
Код:
Столбец C, который был нашим столбцом диапазона, теперь скрыт.
Excel VBA Hide Column - Пример № 4
Есть еще один простой способ автоматизировать процесс скрытия столбцов в VBA. Для этого снова напишите подкатегорию в имени VBA Hide Columns.
Код:
Прямо выберите столбец, который нам нужно скрыть. Здесь снова мы выбираем тот же столбец C.
Код:
И выберите скрытую команду как ИСТИНА, чтобы активировать процесс скрытия. Или выберите ЛОЖЬ, чтобы активировать процесс отображения.
Код:
Посмотрим, колонка С снова скрыта.
Плюсы и минусы Excel VBA Скрыть столбец
- Процесс VBA Hide Column очень прост в реализации.
- Мы можем скрыть любой тип столбца Range, будь то в последовательности или в случайных позициях.
- Поскольку процесс VBA Hide Column прост в применении, но в ручном режиме, он также прост, как в коде VBA. Таким образом, это не вносит большой вклад в экономию. Настоятельно рекомендуется только тогда, когда мы работаем над написанием огромного кода.
То, что нужно запомнить
- Мы скрываем и отображаем столбец с помощью некоторого кода VBA. Для отображения, используйте FALSE вместо TRUE для функциональной цели.
- Порядковый номер столбца также позволяет скрыть столбец, кроме выбора имени столбца, которое упоминается в алфавитном порядке.
Рекомендуемые статьи
Это руководство к VBA Hide Columns. Здесь мы обсудим, как скрыть столбцы в Excel, используя код VBA, а также приведем практические примеры и загружаемый шаблон Excel. Вы также можете просмотреть наши другие предлагаемые статьи -
Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех "оптом", используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.
Давайте рассмотрим несколько способов решения этой задачи.
Способ 1. Поиск пустых ячеек
Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.
Предположим, что мы имеем дело вот с такой таблицей, содержащей внутри множество пустых строк и столбцов (для наглядности выделены цветом):
Допустим, мы уверены, что в первом столбце нашей таблицы (колонка B) всегда обязательно присутствует название какого-либо города. Тогда пустые ячейки в этой колонке будут признаком ненужных пустых строк. Чтобы быстро их все удалить делаем следующее:
- Выделяем диапазон с городами (B2:B26)
- Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная - Найти и выделить - Выделить группу ячеек (Home - Find&Select - Go to special) .
- В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
- Теперь выбираем на вкладке Главная команду Удалить - Удалить строки с листа (Delete - Delete rows) или жмём сочетание клавиш Ctrl + минус - и наша задача решена.
Само-собой, от пустых столбцов можно избавиться совершенно аналогично, взяв за основу шапку таблицы.
Способ 2. Поиск незаполненных строк
Как вы, возможно, уже сообразили, предыдущий способ сработает только в том случае, если в наших данных обязательно присутствую полностью заполненные строки и столбцы, за которые можно зацепиться при поиске пустых ячеек. Но что, если такой уверенности нет, и в данных могут содержаться и пустые ячейки в том числе?
Взгляните, например, на следующую таблицу - как раз такой случай:
Здесь подход будет чуть похитрее:
-
Введём в ячейку A2 функцию СЧЁТЗ (COUNTA) , которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:
К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.
Способ 3. Макрос удаления всех пустых строк и столбцов на листе
Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt + F11 или выберите на вкладке Разработчик - Visual Basic (Developer - Visual Basic Editor) . Если вкладки Разработчик не видно, то можно включить ее через Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) .
В открывшемся окне редактора Visual Basic выберите команду меню Insert - Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:
Закройте редактор и вернитесь в Excel.
Теперь нажмите сочетание Alt + F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) - все пустые строки и столбцы на листе будут мгновенно удалены.
Способ 4. Запрос Power Query
Ещё один способ решить нашу задачу и весьма частый сценарий - это удаление пустых строк и столбцов в Power Query.
Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую "умную" сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:
Теперь используем команду Данные - Получить данные - Из таблицы/диапазона (Data - Get Data - From table/range) и грузим всё в Power Query:
Дальше всё просто:
- Удаляем пустые строки командой Главная - Сократить строки - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove empty rows).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована - преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:
Читайте также: