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

Макросы позволяют нам не только быстро форматировать большие таблицы в Excel, а также автоматически изменять их структуру данных. В данном примере мы рассмотрим, как убрать пустые ячейки, объединив их таким образом, чтобы таблица приобрела читаемый вид. Вы убедитесь, на сколько макрос незаменим при подготовке больших таблиц для визуального анализа данных.

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

Допустим у нас иметься таблица отчета по продажам отдельных магазинов целой ритейловской сети. Магазины, находящиеся в одном городе записаны группами под ряд. Но название города введено только в одну ячейку первой строки группы магазинов из этого же города. Соответственно под ячейкой с названием города находятся пустые ячейки до следующего названия города, как показано ниже на рисунке:

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

Необходимо объединить пустые ячейки в столбце B в каждой группе таким образом, чтобы каждая объединенная ячейка получила соответственное название города в своем значении. Тогда отчет будет читабельным и удобным для визуального анализа данных. Выполнение данной задачи вручную потребует много времени и сил, что повышает вероятность возникновения ошибок в структуре таблицы. Поэтому рационально написать макрос, который существенно облегчит процесс преобразования структуры таблицы и предотвратит возникновения ошибок, вызванных человеческим фактором. Стоит обратить внимание что в группах разное количество пустых ячеек.

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

И создадим новый модуль с помощью инструмента в редакторе: «Insert»-«Module». И запишем в него VBA-код макроса:

Sub JoinEmpty() Dim i As Long Dim pusto As Long pusto = 0 For i = Selection.Rows.Count To 1 Step -1 If Selection.Cells(i, 1) = "" Then pusto = pusto + 1 ElseIf pusto > 0 Then ActiveSheet.Range(Selection.Cells(i, 1), Selection.Cells(i + pusto, 1)).Merge Selection.Cells(i, 1).VerticalAlignment = xlVAlignCenter pusto = 0 End If Next End Sub

Теперь если нам нужно автоматически объединять диапазоны групп магазинов, чтобы избавиться от пустых ячеек, выделите диапазон B2:B17. А потом запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«JoinEmpty»-«Выполнить». Ячейки объединяться в соответствии со всеми требованиями пользователя, а результат выполнения макроса показан на рисунке:

В коде определены 2 переменные:

i – хранит в себе количество циклов соответственное количеству строк в выделенном диапазоне.
pusto – хранит в себе количество пустых ячеек.

Сначала для второй переменной pusto присваиваем число 0 в качестве первого значения. Далее циклом проходим по всех ячейках в первом столбце выделенного диапазона. Каждую ячейку проверяем, является ли она пустой. Если да тогда увеличиваем числовое значение в переменной pusto на 1. А если же ячейка не пустая и в переменной pusto значение больше чем 0, объединяем все ячейки, которые находятся ниже (с ней же включительно). Количество объединяемых ячеек, расположенных ниже зависит от текущего числа в переменной pusto

В конце цикла, благодаря установленному свойству VerticalAlignment, включаем выравнивание текста в центре по вертикали для текущей объединенной ячейки. Далее снова обнуляем значение для переменной pusto, которая отвечает за количество пустых ячеек в цикле.

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

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

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

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

Dim j As Long

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

For j = 1 To Selection.Columns.Count

В конце кода не забудем добавить конец нового цикла:

Теперь следует внести изменения для ссылок аргументов внутри нового цикла. Во всех экземплярах объекта Cells, во втором аргументе вместо числа 1 введем переменную j:

Selection.Cells(i,j)

Полная версия измененного кода выглядит так:

Sub JoinEmpty() Dim i As Long Dim pusto As Long Dim j As Long pusto = 0 For j = 1 To Selection.Columns.Count For i = Selection.Rows.Count To 1 Step -1 If Selection.Cells(i, j) = "" Then pusto = pusto + 1 ElseIf pusto > 0 Then ActiveSheet.Range(Selection.Cells(i, j), Selection.Cells(i + pusto, j)).Merge Selection.Cells(i, j).VerticalAlignment = xlVAlignCenter pusto = 0 End If Next Next End Sub

Теперь достаточно выделить целую таблицу и запустить новую версию макроса:

Как видите теперь макрос можно применять одновременно для нескольких столбцов.

Внимание! Если первая ячейка (в верхнем левом углу) выделенного диапазона – пуста, то она не будет объединена, а также не будут объединены пустые ячейки, находящиеся непосредственно под ней.