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

This tutorial will demonstrate different ways to delete rows and columns in Excel using VBA.

Delete Entire Row or Column

To delete an entire row in VBA use this line of code:

Rows(1).Delete

Notice we use the Delete method to delete a row.

Instead of referencing the Rows Object, you can reference rows based on their Range Object with EntireRow:

Range("a1").EntireRow.Delete

Similarly to delete an entire column, use these lines of code:

Columns(1).Delete

Range("a1").EntireColumn.Delete

Delete Multiple Rows or Columns

Using the same logic, you can also delete multiple rows at once:

Rows("1:3").Delete

or columns:

Columns("A:C").Delete

Notice here we reference the specific row and column numbers / letters surrounded by quotations.

Of course, you can also reference the EntireRow of a range:

Range("a1:a10").EntireRow.Delete

Note: The examples below only demonstrate deleting rows, however as you can see above, the syntax is virtually identically to delete columns.

Delete Blank / Empty Rows

This example will delete a row if the entire row is blank:

Sub DeleteRows_EntireRowBlank()

Dim cell As Range

For Each cell In Range("b2:b20")
    If Application.WorksheetFunction.CountA(cell.EntireRow) = 0 Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

It makes use of the Excel worksheet function: COUNTA.

Delete Row if Cell is Blank

This will delete a row if specific column in that row is blank (in this case column B):

Range("b3:b20").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Delete Row Based on Cell Value

This will loop through a range, and delete rows if a certain cell value in that row says “delete”.

Sub DeleteRowswithSpecificValue()

Dim cell As Range

For Each cell In Range("b2:b20")
    If cell.Value = "delete" Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

More Delete Row and Column Examples

Delete Duplicate Rows

This code will delete all duplicate rows in a range:

Range("b2:c100").RemoveDuplicates Columns:=2

Notice we set Columns:=2. This tells VBA to check both the first two columns of data when considering if rows are duplicates. A duplicate is only found when both columns have duplicate values.

If we had set this to 1, only the first row would’ve been checked for duplicate values.

Delete Table Rows

This code will delete the second row in a Table by referencing ListObjects.

ThisWorkbook.Sheets("Sheet1").ListObjects("list1").ListRows(2).Delete

Delete Filtered Rows

To delete only rows that are visible after filtering:

Range("b3:b20").SpecialCells(xlCellTypeVisible).EntireRow.Delete

VBA Programming | Code Generator does work for you!

Delete Rows in Range

This code will delete all rows in range:

Range("a1:a10").EntireRow.Delete

Delete Selected Rows

This code will delete all selected rows:

Selection.EntireRow.Delete

Delete Last Row

This will delete the last used row in column B:

Cells(Rows.Count, 2).End(xlUp).EntireRow.Delete

By changing 2 to 1, you can delete the last used row in column A, etc.:

Cells(Rows.Count, 1).End(xlUp).EntireRow.Delete

Delete Columns by Number

To delete a column by it’s number, use a code like this:

Columns (2).Delete