Как объединить ячейки в Excel с помощью кода макроса VBA

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

Автоматическое объединение большого количества ячеек по вертикали

Допустим мы имеем маркетинговый план внедрения нового информационного программного продукта:

Чтобы план было легче визуально анализировать лучше объединить ячейки этапов выполнения плана: A2:A4, B2:B4 и т.д. К сожалению, многократно объединять диапазоны с большим количеством строк вручную – это задание требует слишком много времени и сил. Кроме того, можно допустить много ошибок после очередного десятка выделения ячеек перед объединением. Рассмотрим каким способом можно существенно облегчить свой труд переложив большую часть работы на простую программу, написанную на языке VBA прямо в Excel. Для этого следует написать макрос, который безошибочно быстро и автоматически объединит ячейки диапазонов с разным количеством строк для каждого столбца.

Сначала откройте редактор макросов: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (или просто нажмите ALT+F11).

Откройте стандартный модуль выбрав инструмент в редакторе: «Insert»-«Module» и введите в него следующий код макроса для объединения ячеек:

Sub ObedenitVertikal() Dim i As Long Dim j As Long Dim intext As String Application.DisplayAlerts = False For i = 1 To Selection.Columns.Count intext = Selection.Cells(1, i) For j = 2 To Selection.Rows.Count intext = intext & Chr(10) & Selection.Cells(j, i) Next Selection.Columns(i).Merge Selection.Cells(1, i) = intext Next Application.DisplayAlerts = True End Sub

Пока что это еще не полная версия макроса поэтому перед тем как его проверить нам все еще вручную необходимо выделить первый диапазон A2:D4. После чего можно выполнить нашу первую версию макроса.

Запуск макроса для объединения ячеек

Выбираем инструмент: «РАЗРАБОТЧИК»-«Код»-«Макросы».

В появившемся диалоговом окне выделяем значение «ObedenitVertikal» и нажимаем на кнопку «Выполнить».

Потом снова вручную выделите новый диапазон A5:D9 и повторно выполните тот же макрос. Ячейки будут выделены как показано ниже на рисунке:

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

Символ обрыва строки вставлен с помощью функции Chr(10). Каждый вводимый символ из клавиатуры имеет свой код ASCII. Если введем код 10 в качестве аргумента для функции Chr(), тогда она будет возвращать символ обрыва строки. Такой же код ASCII на клавиатуре вызывается клавишей Enter для обрыва строки во всех текстовых редакторах.

Потом остальной код макроса объединяет все ячейки столбцов в выделенном диапазоне, а потом заполняет их текстом из текстовой переменной. Чтобы не появлялось предупреждающее сообщение об объединении ячеек, в начале кода программы макроса отключаем отображение сообщений в Excel средствами программирования. Для этой цели используем свойство: Application.DisplayAlerts = Fale. После выполнения кода макроса изменяем значение свойства на True, чтобы в дальнейшем процессе работы с программой Excel все сообщения предупреждений (Alerts) имели возможность отображаться.

Внимание! Если ячейки выделенного диапазона будут содержать формулы, то после выполнения макроса эти формулы будут заменены на текст. В результате после объединения ячеек макросом, формулы могут быть утеряны.

Модернизация и настройка кода макроса для объединения ячеек

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

intext = intext & Chr(10) & “-----” & Chr(10) & Selection.Cells(j, i)

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

Если нам нужно чтобы выполнять макрос после выделения нескольких диапазонов (с удержанием клавиши CTRL), тогда можно добавить еще одну переменную, которая будет дополнять функцию счетчика:

Dim k As Long

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

For k = 1 To Selection.Areas.Count

А после последнего цикла добавим строку конца нового цикла:

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

Selection.Areas(k)

Полная новая версия макроса для объедения ячеек выделенных нескольких диапазонов, выглядит так:

Sub ObedenitVertikal() Dim i As Long Dim j As Long Dim k As Long Dim intext As String Application.DisplayAlerts = False For k = 1 To Selection.Areas.Count For i = 1 To Selection.Areas(k).Columns.Count intext = Selection.Areas(k).Cells(1, i) For j = 2 To Selection.Areas(k).Rows.Count intext = intext & Chr(10) & Selection.Areas(k).Cells(j, i) Next Selection.Areas(k).Columns(i).Merge Selection.Areas(k).Cells(1, i) = intext Next Next Application.DisplayAlerts = True End Sub

Тепер выделяем 2 диапазона подряд A2:D4, A5:D8, A с нажатой клавишей CTRL на клавиатуре:

В результате получаем идентичный вид таблицы с объединенными ячейками: