Как удалить столбец в excel макрос

Удаление пустых строк и столбцов в данных

Пустые строки и столбцы могут быть головной болью в таблицах во многих случаях. Стандартные функции сортировки, фильтрации, подведения итогов, создания сводных таблиц и т.д. воспринимают пустые строки и столбцы как разрыв таблицы, не подхватывая данные, расположенные за ними далее. Если таких разрывов много, то удалять их вручную может оказаться весьма затратно, а удалить сразу всех «оптом», используя фильтрацию не получится, т.к. фильтр тоже будет «спотыкаться» на разрывах.

Давайте рассмотрим несколько способов решения этой задачи.

Способ 1. Поиск пустых ячеек

Это, может, и не самый удобный, но точно самый простой способ вполне достойный упоминания.

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

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

1. Выделяем диапазон с городами (B2:B26)
2. Нажимаем клавишу F5 и затем кнопку Выделить (Go to Special) или выбираем на вкладке Главная — Найти и выделить — Выделить группу ячеек (Home — Find&Select — Go to special).
3. В открывшемся окне выбираем опцию Пустые ячейки (Blanks) и жмём ОК – должны выделиться все пустые ячейки в первом столбце нашей таблицы.
4. Теперь выбираем на вкладке Главная команду Удалить — Удалить строки с листа (Delete — Delete rows) или жмём сочетание клавиш Ctrl+минус — и наша задача решена.

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

Способ 2. Поиск незаполненных строк

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

Взгляните, например, на следующую таблицу — как раз такой случай:

Здесь подход будет чуть похитрее:

1. Введём в ячейку A2 функцию СЧЁТЗ (COUNTA), которая вычислит количество заполненных ячеек в строке правее и скопируем эту формулу вниз на всю таблицу:

   

2. Выделим ячейку А2 и включим фильтр командой Данные — Фильтр (Data — Filter) или сочетанием клавиш Ctrl+Shift+L.
3. Отфильтруем по вычисленному столбцу нули, т.е. все строки, где нет данных.
4. Осталось выделить отфильтрованные строки и удалить их командой Главная — Удалить -’ Удалить строки с листа (Home — Delete — Delete rows) или сочетанием клавиш Ctrl+минус.
5. Отключаем фильтр и получаем наши данные без пустых строк.

К сожалению, со столбцами такой трюк уже не проделать – фильтровать по столбцам Excel пока не научился.

Способ 3. Макрос удаления всех пустых строк и столбцов на листе

Для автоматизации подобной задачи можно использовать и простой макрос. Нажмите сочетание клавиш Alt+F11 или выберите на вкладке Разработчик — Visual Basic (Developer — Visual Basic Editor). Если вкладки Разработчик не видно, то можно включить ее через Файл — Параметры — Настройка ленты (File — Options — Customize Ribbon).

В открывшемся окне редактора Visual Basic выберите команду меню Insert — Module и в появившийся пустой модуль скопируйте и вставьте следующие строки:

 
Sub DeleteEmpty()
    Dim r As Long, rng As Range

    'удаляем пустые строки
    For r = 1 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
        If Application.CountA(Rows(r)) = 0 Then
            If rng Is Nothing Then Set rng = Rows(r) Else Set rng = Union(rng, Rows(r))
        End If
    Next r
    If Not rng Is Nothing Then rng.Delete
    
    'удаляем пустые столбцы
    Set rng = Nothing
    For r = 1 To ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
        If Application.CountA(Columns(r)) = 0 Then
            If rng Is Nothing Then Set rng = Columns(r) Else Set rng = Union(rng, Columns(r))
        End If
    Next r
    If Not rng Is Nothing Then rng.Delete

End Sub

Закройте редактор и вернитесь в Excel. 

Теперь нажмите сочетание Alt+F8 или кнопку Макросы на вкладке Разработчик. В открывшемся окне будут перечислены все доступные вам в данный момент для запуска макросы, в том числе только что созданный макрос DeleteEmpty. Выберите его и нажмите кнопку Выполнить (Run) — все пустые строки и столбцы на листе будут мгновенно удалены.

Способ 4. Запрос Power Query

Ещё один способ решить нашу задачу и весьма частый сценарий — это удаление пустых строк и столбцов в Power Query.

Сначала давайте загрузим нашу таблицу в редактор запросов Power Query. Можно конвертировать её в динамическую «умную» сочетанием клавиш Ctrl+T или же просто выделить наш диапазон данных и дать ему имя (например Данные) в строке формул, преобразовав в именованный:

Теперь используем команду Данные — Получить данные — Из таблицы/диапазона (Data — Get Data — From table/range) и грузим всё в Power Query:

Дальше всё просто:

1. Удаляем пустые строки командой Главная — Сократить строки — Удалить строки — Удалить пустые строки (Home — Remove Rows — Remove empty rows).
2. Щёлкаем правой кнопкой мыши по заголовку первого столбца Город и выбираем в контекстном меню команду Отменить свёртывание других столбцов (Unpivot Other Columns). Наша таблица будет, как это технически правильно называется, нормализована — преобразована в три столбца: город, месяц и значение с пересечения города и месяца из исходной таблицы. Особенность этой операции в Power Query в том, что она пропускает в исходных данных пустые ячейки, что нам и требуется:

   

3. Теперь выполяем обратную операцию — сворачиваем полученную таблицу обратно в двумерную, чтобы вернуть ей исходный вид. Выделяем столбец с месяцами и на вкладке Преобразование выбираем команду Столбец сведения (Transform — Pivot Column). В открывшемся окне в качестве столбца значений выбираем последний (Значение), а в расширенных параметрах — операцию Не агрегировать (Don’t aggregate):

   

4. Останется выгрузить результат обратно в Excel командой Главная — Закрыть и загрузить — Закрыть и загрузить в… (Home — Close&Load — Close&Load to…)

   

Ссылки по теме

• Что такое макрос, как он работает, куда копировать текст макроса, как запустить макрос?
• Заполнение всех пустых ячеек в списке значениями вышестоящих ячеек
• Удаление всех пустых ячеек из заданного диапазона
• Удаление всех пустых строк на листе с помощью надстройки PLEX

В данном примере предоставлены и описаны исходные коды VBA-макросов для работы с пустыми столбцами в таблице Excel.

Как выделить все пустые столбцы макросом

У нас иметься таблица годового бюджета с поквартальными показателями сумм статей расходов:

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

Откройте редактор кода макросов Visual Basic (ALT+F11):

В редакторе создайте новый модуль выбрав инструмент: «Insert»-«Module» и введите в него этот VBA-код макроса:

Sub SelectColumn()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Columns.Count
If WorksheetFunction.CountA(diapaz1.Columns(i).EntireColumn) = 0 Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1.Columns(i).EntireColumn
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1.Columns(i).EntireColumn)
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодного пустого столбца!"
Else
diapaz2.Select
End If
End Sub


Теперь если нам нужно удалить пустые столбцы из таблицы годового бюджета, выберите инструмент: «РАЗРАБОТЧИК»-«Выполнить»-«Макросы»-«SelectColumn»-«Выполнить».

Все пустые столбцы автоматически выделены. Теперь достаточно только воспользоваться встроенным инструментом Excel: «ГЛАВНАЯ»-«Ячейки»-«Удалить»-«Удалить строки с листа». Или нажать комбинацию горячих клавиш CTRL+=. А после в появившемся окне «Удаление ячеек» выбрать опцию «столбец» и нажать ОК.

В начале кода мы описываем диапазон ячеек для переменной diapaz1, с которым будем работать. Он находиться в границах между ячейкой A1 и последней используемой ячейкой на рабочем листе Excel.

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

Далее в цикле поочередно проверяется каждый столбец в диапазоне ячеек определенным в переменной diapaz1. Если в столбце количество ячеек со значением равно 0, то целый столбец добавляется к несмежному диапазону, определенному в переменной diapaz2.

В конце кода проверяется: если в таблице не найдено ни одного пустого столбца, тогда выводиться соответственное сообщение. Если же пустые столбцы присутствуют, тогда все они одновременно выделяются несмежным диапазоном с помощью метода для переменной diapaz2.Select



Макрос для удаления пустых столбцов

Как удалить пустые столбцы макросом? Если нужно сделать так чтобы макрос автоматически не только выделял, но и сам удалял пустые целые и вертикальные диапазоны ячеек без использования других инструментов, тогда в конце кода для переменной diapaz2.Select следует изменить метод на [Delete]:

diapaz2.[Delete]

Удалить пустые столбцы макросом:

Sub DelColumn()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Columns.Count
If WorksheetFunction.CountA(diapaz1.Columns(i).EntireColumn) = 0 Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1.Columns(i).EntireColumn
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1.Columns(i).EntireColumn)
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодного пустого столбца!"
Else
diapaz2.[Delete]
End If
End Sub


Пример второго VBA-кода:

Макрос для скрытия пустых столбцов

Как скрыть пустые столбцы макросом? Но если вам нужно не удалить, а только скрыть (например, при подготовке документа на печать), тогда эту строку кода следует модифицировать несколько иначе:

diapaz2.EntireRow.Hidden = True

Скрыть пустые столбцы макросом:

Sub HidColumn()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Columns.Count
If WorksheetFunction.CountA(diapaz1.Columns(i).EntireColumn) = 0 Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1.Columns(i).EntireColumn
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1.Columns(i).EntireColumn)
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодного пустого столбца!"
Else
diapaz2.EntireColumn.Hidden = True
End If
End Sub


Пример третьего VBA-кода:

Добавление пустых столбцов макросом

Как вставить столбец макросом? Если мы изменим код в этом же месте как показано ниже, то получиться инструмент для добавления и вставки строк после пустых:

diapaz2.[Insert]

Добавить пустые столбцы макросом:

Sub AddColumn()
  Dim i As Long
  Dim diapaz1 As Range
  Dim diapaz2 As Range
Set diapaz1 = Application.Range(ActiveSheet.Range("A1"), _
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell))
For i = 1 To diapaz1.Columns.Count
If WorksheetFunction.CountA(diapaz1.Columns(i).EntireColumn) = 0 Then
If diapaz2 Is Nothing Then
Set diapaz2 = diapaz1.Columns(i).EntireColumn
Else
Set diapaz2 = Application.Union(diapaz2, diapaz1.Columns(i).EntireColumn)
End If
End If
Next
If diapaz2 Is Nothing Then
MsgBox "Ненайдено ниодного пустого столбца!"
Else
diapaz2.[Insert]
End If
End Sub


Пример четвертого VBA-кода:

Если же вы хотите, чтобы макрос работал исключительно только для пустых столбцов предварительно выделенного определенного диапазона листа перед запуском макроса, то в начале макроса следует изменить строку создания экземпляра объекта для переменной diapaz1, на:

Читайте также: Как выделить строки в Excel макросом.

Внимание! Следует помнить о том, что если таким образом создавать экземпляр объекта для переменной diapaz1, то тогда нельзя перед запуском макроса выделять все ячейки листа или все ячейки любого столбца, строки. Иначе это затормозит программу Excel, так как один лист содержит аж 1 048 576 строк и тогда они все будут обрабатываться макросом, на что потребуются дополнительные ресурсы системы и время.

Skip to content

Sub UdalitPustieStolbci()
' Шаг 1: объявляем переменные
Dim MyRange As Range
Dim iCounter As Long
'Шаг 2: Определяем целевой диапазон 
Set MyRange = ActiveSheet.UsedRange
'Шаг 3: Запускаем обратный цикл через диапазон
For iCounter = MyRange.Columns.Count To 1 Step -1
'Шаг 4: Если весь столбец пуст, то удаляем его
If Application.CountA(Columns(iCounter).EntireColumn) = 0 Then
Columns(iCounter).Delete
End If
' Шаг 5: Увеличиваем счетчик
Next iCounter
End Sub