Bazaprogram.ru

Новости из мира ПК
4 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel объединение ячеек

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

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

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

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

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

  1. Сначала откройте редактор макросов: «РАЗРАБОТЧИК»-«Код»-«Visual Basic» (или просто нажмите ALT+F11).
  2. Откройте стандартный модуль выбрав инструмент в редакторе: «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» и нажимаем на кнопку «Выполнить».

Читать еще:  Дмакс в excel примеры

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

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

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

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

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

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

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

Читать еще:  Учебник vba excel

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

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

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

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

For k = 1 To Selection.Areas.Count

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

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

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

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 на клавиатуре:

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

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

Vba excel объединение ячеек

Профиль | Отправить PM | Цитировать

——-
Забудем боль, забудем страх —
И только ветер в парусах!

Сообщения: 25778
Благодарности: 7508

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

Читать еще:  Как рассчитать ранг в excel

Сообщения: 306
Благодарности: 3

Вложения

1.rar
(9.4 Kb, 1 просмотров)

——-
Забудем боль, забудем страх —
И только ветер в парусах!

Сообщения: 25778
Благодарности: 7508

Это сообщение посчитали полезным следующие участники:

Сообщения: 306
Благодарности: 3

——-
Забудем боль, забудем страх —
И только ветер в парусах!

Сообщения: 25778
Благодарности: 7508

необходимость обьединять последовательно сначала второй, потом третий, потом произвольный, а в самом конце первый столбец »

Сообщения: 25778
Благодарности: 7508

в текущем коде ничего непонял. »

Там достаточно просто.

Работаем с первым столбцом использованного диапазона (.UsedRange) активного рабочего листа (ActiveSheet). Предполагаем, что данные тупо расположены с первой же ячейки и имеют заголовки столбцов. Посему начинаем с того, что диапазону для объединения (objRange4Union) присваиваем диапазон, состоящий из ячейки A2, и запоминаем значение из той же самой ячейки A2. Далее перебираем все строки из первого столбца от третьей строки до последней строки использованного диапазона.

Если значение в очередной ячейке совпадает с запомненным значением (из ячейки выше) — включаем в диапазон для объединения эту ячейку (путём операции Union() над ним самим и этой самой очередной ячейкой).

Если не совпадает — то на всякий случай сначала проводим разъединение ячеек, расположенными в столбце правее (.Offset(0, 1)) диапазона для объединения (objRange4Union), затем объединение этих ячеек, делаем выравнивание содержимого. Ту же операцию проделываем с ячейками, расположенными на два стобца правее (.Offset(0, 2)) диапазона для объединения. Наконец, начинаем заново собирать новый диапазон для объединения, начиная с текущей очередной ячейки.

После завершения проверки условия в конце цикла запоминаем в переменной значение текущей очередной ячейки и повторяем весь цикл.

По завершении цикла нам остаётся повторить операции объединения над текущим вычисленным диапазоном для объединения.

Ссылка на основную публикацию
Adblock
detector