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

122634 14.10.2012 Скачать пример

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

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

Способ 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:

Загруженные в Power Query данные

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

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

    Отмена свертывания (unpivot) таблицы

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

    Сворачиваем обратно

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

    Результат

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



MCH
25.09.2013 20:20:17
Если удаляемых строк будет много, то данный макрос будет работать очень медленно
Для ускорения можно использовать такой вариант:
Sub DeleteEmptyRows()
    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
End Sub
qwerty123123123
09.10.2013 20:44:03
А как удалить только в выделенном диапазоне (за пределами строки могут быть и не пустые, но должны удалиться) ?
25.10.2013 07:45:13
Переписать макрос, например, вот так - удаляет пустые строки в выделенном диапазоне, а не на всем листе:
Sub DeleteEmptyRowsInRange()
    Dim r As Long, rng As Range
    For r = 1 To Selection.Rows.Count
        If Application.CountA(Selection.Rows(r)) = 0 Then
            If rng Is Nothing Then
                Set rng = Selection.Rows(r)
            Else
                Set rng = Union(rng, Selection.Rows(r))
            End If
        End If
    Next r
    If Not rng Is Nothing Then rng.Delete
End Sub

22.08.2014 17:17:51
Здравствуйте Николай! Спасибо за такой полезный и очень познавательный сайт! Не могли бы подсказать, возможно ли при удалении пустых строк с выделенного диапазона, нижние строки не поднимались вверх, а оставались на месте? А то эти нижние строки съезжают при удалении верхних строк. Это реально или нет? Буду очень благодарна за подсказку.

И еще как можно поменять код, чтобы вместо Selection.Rows были конкретные строки, например только для диапазона Range("B3:B10";)? Пока решение нашла такое, путем копирования диапазона в другое место. Ну кажется есть решение по красивее.
Sub DeleteEmptyRowsInRange()
    Dim r As Long, rng As Range
    Range("B3:B10").Copy Range("H3:H10")
    Range("H3:H10").Select
    For r = 1 To Selection.Rows.Count
        If Application.CountA(Selection.Rows(r)) = 0 Then
            If rng Is Nothing Then
                Set rng = Selection.Rows(r)
            Else
                Set rng = Union(rng, Selection.Rows(r))
            End If
        End If
    Next r
    If Not rng Is Nothing Then rng.Delete
     Range("H3:H10").Copy Range("B3:B10")
     Range("H3:H10").ClearContents
End Sub 
Скажите, как изменить код, чтобы проверялась не вся строка в выделенном диапазоне,
а вся строка без первой ячейке в ней, и вот если в этой строке (без первой ячейки) пусто,
то удалить строку, включая первую ячейку, т.е. как тут и есть?
23.10.2013 09:50:20
подскажите как удалить строки с пустым значением ""?

поменял в коде 0 на "" - не получается.
25.10.2013 07:46:01
Уточните, что вы подразумеваете под пустым значением ""? Оно получается в результате работы формулы?
25.10.2013 09:43:01
да, в результате функции ЕСЛИ, возвращает пустое значение "" либо приравнивает к другой ячейке.
28.10.2013 09:42:08
Значит ячейка не пустая - в ней функция. Придется использовать другой макрос.
28.03.2014 21:00:08
Здравствуйте! А какой  макрос нужен чтоб удалялись пустые строки, но в которой есть функции? Т.е  изначально функции задавались как в случае ЛОЖЬ чтоб была пустая ячейка. Желательно для целого листа. СПАСИБО!
Здравствуйте! После перевода формул в значения на листе методом "копировать - вставить как значение" этот макрос не сработал на ячейки с возвращенным пустым ("") значением. Подскажите как это решить.
25.10.2013 23:03:24
Здравствуйте! Воспользовался данным макросом. Но так как мне нужно удалить непустые строки, изменил = 0 на <> 0

If Application.CountA(Rows(r)) <> 0 Then Rows(r).Delete   'если в строке пусто - удаляем ее
Теперь выскакивает ошибка: метод Delete из класса Rаnde завершен не верно, что исправить?
28.10.2013 09:36:31
Объединенных ячеек на листе нет, часом? Скорее всего, они мешают.
02.12.2013 20:18:52
подскажите пожалуйста, как удалить пустые столбцы с листа
02.12.2013 20:23:08
Переписать макрос примерно так:
Sub DeleteEmptyColumns()
    LastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count    
    Application.ScreenUpdating = False
    For r = LastCol To 1 Step -1         
        If Application.CountA(Columns(r)) = 0 Then Columns(r).Delete
    Next r
End Sub
09.12.2013 18:35:55
Спасибо!
10.12.2013 08:13:43
Подскажите пожалуйста. Есть заполненные строки в листе, стоит задача под каждой заполненной строкой поставить пустую строчку
11.12.2013 10:47:44
Боюсь, это только макросом.
И что значит, в вашем понимании, "заполненная строка"?
11.12.2013 11:06:31
1113222333322
11211211212121
12111212112121
Ячейки заполненные текстом или цифрами. Стоит задача под каждой заполненной ячейкой проставить пустую строчку. Количество заполненных ячеек всегда разное
Спасибо большое за макрос!!!

Выручил многократно, работа набирает темпы.
19.03.2015 16:48:17
Добрый день! Подскажите, пожалуйста, как подправить макрос, чтобы удалялись строки содержащие 0 в колонке 3 , на пример!?
буду очень признательна за помощь.
26.04.2016 13:05:40
Здравствуйте, может кто-нибудь подсказать, как изменить указанный выше макрос, чтобы удалять строки, которые содержат функцию?
17.09.2016 11:24:39
Как удалять строки в зависимости от значений в столбцах таблицы в определенных колонках
Доброго времени суток!
Тоже волнует этот вопрос, который задал предыдущий автор.
19.07.2018 11:35:20
Добрый день!
Подскажите, пожалуйста, как удалить пустые строки в таблице (таблица А8:К999)
Наверх