Vba excel отменить объединение ячеек
Обновлено: 05.07.2024
В программе Excel присутствует кнопка для разъединения объединенных ячеек таблицы на закладке: «ГЛАВНАЯ»-«Выравнивание»-«Отменить объединение ячеек». Но что, если эту операцию нужно выполнять многократно, да еще и после нее заполнять данными ново созданные ячейки. Реализовать данную задачу вручную – это весьма затратное занятие по времени и силам. Здесь рационально воспользоваться макросом.
Макрос для разъединения объединенных ячеек в Excel
Допустим у нас уже имеется вполне читабельная таблица списка заказов, в которой имеются объединенные ячейки в столбце «Год». Пример, такой таблицы изображен ниже на рисунке:
Но нам необходимо преобразовать данную таблицу в стандартный формат, например, для создания отчета на основе сводной таблицы. Для этого откроем редактор Visual Basic (ALT+F11):
И вставим новый стандартный модуль используя инструмент в редакторе: «Insert»-«Module». А после чего запишем в модуль VBA код макроса для разъединения объединенных ячеек:
Sub RazdelitVstavit()
Dim adres As String
adres = ActiveCell.MergeArea.Address
If adres <> ActiveCell.Address Then
ActiveCell.UnMerge
ActiveCell.Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
End Sub
Если мы хотим отменить объединение ячеек в столбце «Год» и заполнить созданные ячейки соответствующими значениями (годами), тогда перейдите на одну большую объединенную ячейку B2 и запустите макрос: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«RazdelitVstavit»-«Выполнить».
В данном VBA коде макроса используется только одна переменная. Она хранит в себе адрес диапазона ячеек B2:B15 которые охватывает одна активная объединенная ячейка.
Адрес активной ячейки отображается в поле «Имя» (напротив строки формул Excel). Но там не отображается полный адрес объединенной ячейки.
Для пользователя в поле «Имя» будет адрес отображаться одинаково, но в макросе их можно различить с помощью методов объекта ActiveCell.MergeArea.Addres. В зависимости какой тип активных ячеек будет возвращен тип адреса – одна ячейка или диапазон. Если активная ячейка не является объединенной, тогда в переменной будет храниться только адрес одной активной ячейки, а не целого диапазона. Далее макрос проверяет является ли текущая активная ячейка – объединенной, с помощью сравнения двух способов получения адреса для одной и той же активной ячейки. Тот способ, который передал адрес в переменную из метода объекта ActiveCell.MergeArea.Addres и обычный – ActiveCell.Addres. Если адрес в переменной и адрес получен обычным способом не совпадает, значит она является объединенной и код выполняется дальше.
С помощью метода объекта ActiveCell.UnMerge выполняется разъединение объединенной активной ячейки. Далее копируется ее содержимое и заполняется диапазон на листе, адрес которого получен из переменной, его же ранее содержала в себе объединенная активная ячейка. После копирования значения для объекта CutCopyMode устанавливается свойство False, чтобы прекратить процесс копирования. В результате таблица листа заказов будет иметь такой же вид как показано ниже на рисунке:
Данный макрос позволяет разъединить объединенные ячейки, которые используют любое направление объединения: как по вертикали, так и полгоризонтали. Ее значение будет одинаково вставлено во все ячейки, созданные после разъединения.
Внимание! Объединенная ячейка может содержать в качестве значения формулы. В такие случаи после запуска макроса эта формула будет вставлена во все ячейки созданных в результате отмены объединения только из относительных ссылок в адресах, поскольку в переменной не будет символа $ необходимого для абсолютного или смешанного адреса.
Как разъединить объединенные ячейки сразу в нескольких диапазонах
Если мы хотим, чтобы данный макрос можно было применить одновременно для нескольких объединенных ячеек в выделенном диапазоне, тогда добавим еще одну переменную, которая дополнит код счетчиком цикла:
Создадим цикл, который будет перемещаться по всем выделенным объединенным ячейкам:
For i = 1 To Selection.Count
В конце кода не забудем добавить конец цикла:
Вместо ссылки на активную ячейку Active.Cell теперь будем использовать ссылку на очередную по счету ячейку в выделенном диапазоне: Selection.(i). Полная версия усовершенствованного макроса выглядит следующим образом:
Sub RazdelitVstavit()
Dim adres As String
Dim i As Long
For i = 1 To Selection.Count
adres = Selection(i).MergeArea.Address
If adres <> Selection(i).Address Then
Selection(i).UnMerge
Selection(i).Copy
ActiveSheet.Paste ActiveSheet.Range(adres)
Application.CutCopyMode = False
End If
Next
End Sub
Цикл, который перемещается по каждой объединенной ячейке выделенного диапазона, каждый раз вызывает 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 для обрыва строки во всех текстовых редакторах.
Внимание! Если ячейки выделенного диапазона будут содержать формулы, то после выполнения макроса эти формулы будут заменены на текст. В результате после объединения ячеек макросом, формулы могут быть утеряны.
Модернизация и настройка кода макроса для объединения ячеек
Если нам нужно изменить текст разделяющий отдельные строки символов содержащийся в целых ячейках, то можно вписать другой код символа, текст или несколько текстов соединенных символом амперсантом (&). Допустим мы хотим вставить между двумя символами разрыва строки текст, состоящий из пяти тире «-----». Тогда данную строку следует модифицировать следующим образом:
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, то объединение строк по столбцам будет выполнено только для первого диапазона.
Как получить/записать значение столбца текущей строки
Добрый день. У меня есть две формы На Form 1 расположен DataGridView который выводит 2 столбца.
нужно цифры из ячейки правого столбца дописать к цифрам из соседней ячейки левого столбца
Суть проблемы в следующем: есть два столбца с цифрами, нужно цифры из ячейки правого столбца.
Пул потоками вывести номер столбца массива, в котором есть минимальный элемент текущей строки
Мне нужно пул потоками вивести номер столпца массива в котором есть минимальный елемент текущей.
Чтобы быстро записать синтаксис команды Find используйте макрорекодер.
m_yuka_r, синтаксис неправильный у вас, но эта ошибка не связана с вашей проблемой. После Else не нужно использовать двоеточие:
m_yuka_r, а что пользователь делает, что возникает событие Change и нужно выбирать: отменить объединение ячеек или нет.
Пользователь объединяет ячейки и вводит в объединённые ячейки данные?
Попробуйте этот код:
Спасибо! Очень дельное замечание!
А по моему вопросу можете чем-нибудь помочь? Может мне вручную прописать макрос и по нажатии кнопки отменять объединение. С подобной проблемой я столкнулась не первый раз, ответ пока нигде не нашла.
Добавлено через 56 секунд
Он мне почему-то автоматически пририсовывает двоеточие))
Добавлено через 7 минут
m_yuka_r, а что пользователь делает, что возникает событие Change и нужно выбирать: отменить объединение ячеек или нет.
Пользователь объединяет ячейки и вводит в объединённые ячейки данные?
А отменить объединение нужно на первом или втором листе?
а что пользователь делает, что возникает событие Change и нужно выбирать: отменить объединение ячеек или нет.Пользователь объединяет ячейки и вводит в объединённые ячейки данные?
Пользователь выбирает последний 3й уровень заголовка, и в том случае если у этого заголовка нет подпунктов, то отменяется объединение ячеек. В эти отмененные ячейки тоже программно будет вноситься другой ниспадающий список. Но это я сама потом доделаю.
А отменить объединение нужно на первом или втором листе? m_yuka_r, в выложенной книге на втором листе в ячейке "D6" такой код работает:и в том случае если у этого заголовка нет подпунктов, то отменяется объединение ячеек. а какой код определяет: есть подпункты или нет подпунктов? Это НЕ переход на след. столбец, а создание объекта - соседней ячейки, с которым происходит работа в операторе With. Т.е. этот оператор НЕ меняет Target.
Поэтому 10-я строка, видимо m_yuka_r, в выложенной книге на втором листе в ячейке "D6" такой код работает:
Да, спасибо большое!
Добавлено через 16 минут
проверяет пуста ли строка s. [эта строка s создается функцией, где просматривается весь текущий подпункт и данные из этого подпункта вносятся в строку] И в зависимости от результата заносит в следующую ячейку (текущей строки и следующего столбца) очередной ниспадающий список. Таким образом я определяю есть ли у меня подпункты в текущем заголовке или нет. В том случае, если s пуста, то отменяется объединение ячейки. У меня иерархический выбор данных. Пользователю проще работать со списками чем вручную вводить данные.
Это НЕ переход на след. столбец, а создание объекта - соседней ячейки, с которым происходит работа в операторе With. Т.е. этот оператор НЕ меняет Target.тоже нужна и понятна. Спасибо.
Добавлено через 11 минут
Я не хотела создавать новую тему, т.к. тут уже все в обсуждении. НО если это необходимо. то я не против.
После отмены объединения мне надо полученным 3 ячейкам присвоить список.
Чуть подправила код:
Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную!
Абсолютно точно уверен, Вам это знакомо: печатаешь текст, поднимаешь глаза на экран и понимаешь, что всё прописано заглавными буквами. Всё стираешь, перепечатываешь текст заново… Больше так не делайте!
В этом посте хочу поделиться очень простой, крайне полезной, и далеко не всем известной горячей клавишей для Word и PowerPoint – Shift+F3.
В описанной ранее ситуации просто выберите текст зажмите шифт и затем нажмите F3:
Выбранный текст моментально переводится из прописного регистра в строчный.
При циклированном применении этой комбинации клавиш текст будет постоянно менять регистр в трёх вариантах - все прописные, все строчные, все прописные первые буквы:
Вот в этом видео я рассказал об этой горячей клавише более подробно. Кроме того, здесь представлен аналог подобного функционала в Excel (ведь там эта комбинация клавиш не работает!). Так что предлагаю посмотреть это видео тоже:
Как удалить фон картинки в Excel / Word / PowerPoint – Просто!
Если вдруг нужно вырезать фон картинки, а под рукой нет Photoshop-а – не беда! Ведь эту задачу можно легко решить и непосредственно в Excel, Word-е, или же в PowerPoint-е.
Возьмем в качестве примера вот эту картинку:
Вырежем ей фон в Excel, и укажем вырезанным символом руки на показатель выручки в Берлине за июль вот в этом небольшом отчете:
Итак, переходим по пути команд: ВСТАВКА -> Рисунки -> Это устройство
Выбираем на компьютере нужную картинку и вставляем её на рабочий лист:
Вот и наша картинка:
Excel сразу пытается угадать, что нужно вырезать, и помечает эти части фиолетовым цветом. Так что теперь просто с помощью карандаша добавления (А) добавляем требуемые области в выбор, а с помощью карандаша удаления (Б) удаляем ненужное:
Ну и размещаем, как хотели, вырезанный указатель:
Вот и всё! Через правый щелчок вырезанную картинку можно легко сохранить в формате PNG (в этом формате прозрачные области картинки остаются таковыми и не заполняются белым цветом).
В этом посте пример приведен с простой картинкой. Тем не менее представленный инструмент вполне не плохо работает и с фотографиями, вот в этом видео я привел пример и советую его также посмотреть:
Забудьте об объединении ячеек, ведь намного лучше поступать вот так!
Точно уверен, что Вам часто приходилось понервничать с объединёнными ячейками!
Возьмём в качестве примера вот такую таблицу:
Выглядит, вроде, неплохо, вот только большим, на первый взгляд невидимым, минусом является то, что ячейки в диапазонах B2:E2 и B8:E8 объединены!
Поэтому если попробовать разом выбрать, например, все значения одного из месяцев, у нас непременно будет расширен выбор сразу на все столбцы таблички:
Наверняка Вы уже и сами пользовались подобным объединением ячеек – и я Вам хочу дать совет – постарайтесь больше этот инструмент не использовать, так как он может быть источником самых разных проблем - начиная от проблем с шорткатами, вплоть до сложностей при написании кода в VBA.
Но что же тогда использовать, спросите Вы, если вот понадобилось именно подобное центрирование содержимого по нескольким ячейкам? Для это намного лучше использовать центрирование содержимого по центру выделения. Разберёмся.
Для этого сперва отменяю объединение ячеек:
После этого выбираю все требуемые ячейки (я выбрал ячейки сразу как в строке 2, так и 8):
Теперь щелкаем по вот этому символу:
Нажимаем на ОК и всё готово (на картинке ниже показано, что объединение ячеек не использовано)!
Теперь все выглядит как нам нужно было, и при этом и в функциональном плане всё работает как должно – вот могу спокойно выбирать нужные ячейки:
Всё описанное я также наглядно показал вот в этом видео, советую его посмотреть:
Быстрые формы ввода данных в Excel за пару секунд – без VBA!
Если в Excel приходится вручную заполнять определенный список в форме таблицы, например вот такой:
То всё достаточно просто – заполнение можно производить построчно, пошагово дополняя по строчке в таблицу:
Одним из возможных решений подобной проблемы является использование форм для ввода данных. При это есть два варианта:
1. Пользовательские формы VBA
2. Быстрые формы ввода данных Excel
При первом варианте нужно уметь программировать в VBA, но и результат намного более качественный и индивидуализированный – фактически, можно создать всё что угодно.
Второй же вариант намного менее индивидуализированный, но зато создаются подобные формы за доли секунд и не требуют абсолютно никаких знаний VBA.
Разберем, как вызывать это самые быстрые формы ввода данных Excel. Для этого сперва нужно требуемую таблицу записи перевести в формат умной таблицы. Для этого выбираем её (1) используем комбинацию клавиш CTRL+T (2) и подтверждаем перевод в формат умной таблицы (3):
В результате получаем вот такую умную таблицу:
Теперь в панели быстрого доступа появилась кнопка вызова формы ввода данных. Выбрав нужную таблицу (1) и нажав на эту кнопку (2), Excel будет нам выводить форму данных с полями, соответствующими столбцам таблицы (3):
Далее просто заполняем поля формы и нажатием на Enter добавляем таблице новую строку с введёнными данными, а один раз добавленную кнопку, конечно, можно так же просто использовать и с любыми другими таблицами.
Тем не менее, конечно, видно, что эта автоматическая форма ввода данных больше в Excel-е не развивается (поэтому она была убрана с ленты) и функционал у нее крайне базовый. Лично я бы поэтому ей пользоваться не стал бы и всё-таки решал бы свои задачи с помощью пользовательских форм VBA. Тем не менее, для общеэрудиционных целей и об этом инструменте в Excel следует знать.
Вот в этом видео я еще более подробно и наглядно рассказал об этом инструменте Excel, советую его также посмотреть:
Читайте также: