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

В данном примере предоставлены и описаны исходные коды 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 строк и тогда они все будут обрабатываться макросом, на что потребуются дополнительные ресурсы системы и время.