Разгруппировать ячейки в excel vba

Обновлено: 06.07.2024

Someone might need this, I think I figured out how to make this work.

First, your code needs to be written in "ThisWorkbook" under Microsoft Excel Objects, as @peachyclean suggests.
Second, take the code that @Sravanthi wrote, and paste to the above mentioned location.

Sub Workbook_Open()
'Update 20140603
Dim xWs As Worksheet
Set xWs = Application.ActiveSheet
Dim xPws As String
xPws = "rfc" ''Application.InputBox("Password:", xTitleId, "", Type:=2)
xWs.Protect Password:=xPws, Userinterfaceonly:=True
xWs.EnableOutlining = True
End Sub

The thing is that you need to be on the sheet which you want to protect but allowing using grouping, and save the workbook and close, without protecting. Now if you open it, the macro starts automatically, it will make the sheet protected with the password "rfc". Now you can use the grouping, the sheet is protected.

Furthermore, I didn't want the to-be-protected sheet active when opening the file, therefore I've modified this part:
Set xWs = Application.ActiveSheet ->
Set xWs = Application.Worksheets("WRITEANYSHEET'SNAMEHERE")

Now it works like charm, sheet named 'WRITEANYSHEET'SNAMEHERE' is protected but the grouping applicable. On the long run, I think the problem will be that if I want to modify this file and keep the solution, I need to unprotect this sheet to make it work on the next opening. I guess you can write another macro to automatically unprotect when closing :)

I hope it helped.

This string looked to be exactly what I needed, as I know nothing about VBA. I was able to get this to work initially but as was pointed out, once you close the spreadsheet and reopen it, it no longer works. I tried to write the code in "ThisWorkbook" as noted but I can't figure out how to do that. I can see "ThisWorkbook" but I don't know how to write in it. Every way I see to create a module, it creates a new module in a the separate "Modules" folder, outside of the "Microsoft Excel Objects" folder. Any suggestions on how to put this code in "ThisWorkbook" ? Hii.. this worked wonders. the only place where i am getting stuck now is that i need to do this for multiple sheets in the workbook. can u pls help with that. do you have visuals for the VBA Code discussed 6 days ago to peachyclean about ThisWorkbook under Microsoft Objects instead of a new module. The functionality is lost when I go back into my workbook To fix the issue of this not working in your file after you've closed it and opened it again, you have to paste the VBA code in "ThisWorkbook" under Microsoft Excel Objects instead of a new module. This will then automatically run the macro every time the file is opened.

I have gotten this code to work. But when I close and reopen I must go to the developer tab, select the macros button, select run and enter the password.

Is there a way to remove the password from the code OR a auto run code that will automatically run the this marco and enter the password?

Всем нам приходится - кому реже, кому чаще - повторять одни и те же действия и операции в Excel. Любая офисная работа предполагает некую "рутинную составляющую" - одни и те же еженедельные отчеты, одни и те же действия по обработке поступивших данных, заполнение однообразных таблиц или бланков и т.д. Использование макросов и пользовательских функций позволяет автоматизировать эти операции, перекладывая монотонную однообразную работу на плечи Excel. Другим поводом для использования макросов в вашей работе может стать необходимость добавить в Microsoft Excel недостающие, но нужные вам функции. Например функцию сборки данных с разных листов на один итоговый лист, разнесения данных обратно, вывод суммы прописью и т.д.

Макрос - это запрограммированная последовательность действий (программа, процедура), записанная на языке программирования Visual Basic for Applications (VBA). Мы можем запускать макрос сколько угодно раз, заставляя Excel выполнять последовательность любых нужных нам действий, которые нам не хочется выполнять вручную.

Способ 1. Создание макросов в редакторе Visual Basic

Для ввода команд и формирования программы, т.е. создания макроса необходимо открыть специальное окно - редактор программ на VBA, встроенный в Microsoft Excel.

В старых версиях (Excel 2003 и старше) для этого идем в меню Сервис - Макрос - Редактор Visual Basic(Toos - Macro - Visual Basic Editor).
В новых версиях (Excel 2007 и новее) для этого нужно сначала отобразить вкладку Разработчик (Developer) . Выбираем Файл - Параметры - Настройка ленты (File - Options - Customize Ribbon) и включаем в правой части окна флажок Разработчик (Developer) . Теперь на появившейся вкладке нам будут доступны основные инструменты для работы с макросами, в том числе и нужная нам кнопка Редактор Visual Basic(Visual Basic Editor)

:

К сожалению, интерфейс редактора VBA и файлы справки не переводятся компанией Microsoft на русский язык, поэтому с английскими командами в меню и окнах придется смириться:

Макросы (т.е. наборы команд на языке VBA) хранятся в программных модулях. В любой книге Excel мы можем создать любое количество программных модулей и разместить там наши макросы. Один модуль может содержать любое количество макросов. Доступ ко всем модулям осуществляется с помощью окна Project Explorer в левом верхнем углу редактора (если его не видно, нажмите CTRL+R). Программные модули бывают нескольких типов для разных ситуаций:

Обычный макрос, введенный в стандартный модуль выглядит примерно так:

Давайте разберем приведенный выше в качестве примера макрос Zamena:

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

Способ 2. Запись макросов макрорекордером

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

Макрорекордер записывает только те действия, которые выполняются в пределах окна Microsoft Excel. Как только вы закрываете Excel или переключаетесь в другую программу - запись останавливается.
Макрорекордер может записать только те действия, для которых есть команды меню или кнопки в Excel. Программист же может написать макрос, который делает то, что Excel никогда не умел (сортировку по цвету, например или что-то подобное).
Если во время записи макроса макрорекордером вы ошиблись - ошибка будет записана. Однако смело можете давить на кнопку отмены последнего действия (Undo) - во время записи макроса макрорекордером она не просто возрвращает Вас в предыдущее состояние, но и стирает последнюю записанную команду на VBA.

Чтобы включить запись необходимо:

в Excel 2003 и старше - выбрать в меню Сервис - Макрос - Начать запись(Tools - Macro - Record New Macro)
в Excel 2007 и новее - нажать кнопку Запись макроса (Record macro) на вкладке Разработчик (Developer)

Затем необходимо настроить параметры записываемого макроса в окне Запись макроса:

Имя макроса - подойдет любое имя на русском или английском языке. Имя должно начинаться с буквы и не содержать пробелов и знаков препинания.
Сочетание клавиш - будет потом использоваться для быстрого запуска макроса. Если забудете сочетание или вообще его не введете, то макрос можно будет запустить через меню Сервис - Макрос - Макросы - Выполнить(Tools - Macro - Macros - Run) или с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или нажав ALT+F8.
Сохранить в. - здесь задается место, куда будет сохранен текст макроса, т.е. набор команд на VBA из которых и состоит макрос.:
- Эта книга - макрос сохраняется в модуль текущей книги и, как следствие, будет выполнятся только пока эта книга открыта в Excel
- Новая книга - макрос сохраняется в шаблон, на основе которого создается любая новая пустая книга в Excel, т.е. макрос будет содержаться во всех новых книгах, создаваемых на данном компьютере начиная с текущего момента
- Личная книга макросов - это специальная книга Excel с именем Personal.xls, которая используется как хранилище макросов. Все макросы из Personal.xls загружаются в память при старте Excel и могут быть запущены в любой момент и в любой книге.
После включения записи и выполнения действий, которые необходимо записать, запись можно остановить командой Остановить запись (Stop Recording) .

Запуск и редактирование макросов

Управление всеми доступными макросами производится в окне, которое можно открыть с помощью кнопки Макросы (Macros) на вкладке Разработчик (Developer) или - в старых версиях Excel - через меню Сервис - Макрос - Макросы (Tools - Macro - Macros) :
- Любой выделенный в списке макрос можно запустить кнопкой Выполнить(Run) .
- Кнопка Параметры(Options) позволяет посмотреть и отредактировать сочетание клавиш для быстрого запуска макроса.
- Кнопка Изменить(Edit) открывает редактор Visual Basic (см. выше) и позволяет просмотреть и отредактировать текст макроса на VBA.
Создание кнопки для запуска макросов

Чтобы не запоминать сочетание клавиш для запуска макроса, лучше создать кнопку и назначить ей нужный макрос. Кнопка может быть нескольких типов:

Кнопка на панели инструментов в Excel 2003 и старше

Откройте меню Сервис - Настройка (Tools - Customize) и перейдите на вкладку Команды (Commands) . В категории Макросы легко найти веселый желтый "колобок" - Настраиваемую кнопку (Custom button) :

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

Кнопка на панели быстрого доступа в Excel 2007 и новее

Щелкните правой кнопкой мыши по панели быстрого доступа в левом верхнем углу окна Excel и выберите команду Настройка панели быстрого доступа (Customise Quick Access Toolbar) :

Затем в открывшемся окне выберите категорию Макросы и при помощи кнопки Добавить (Add) перенесите выбранный макрос в правую половину окна, т.е. на панель быстрого доступа:

Кнопка на листе

Этот способ подходит для любой версии Excel. Мы добавим кнопку запуска макроса прямо на рабочий лист, как графический объект. Для этого:
- В Excel 2003 и старше - откройте панель инструментов Формы через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms)
- В Excel 2007 и новее - откройте выпадающий список Вставить (Insert) на вкладке Разработчик (Developer)
Выберите объект Кнопка (Button) :

Затем нарисуйте кнопку на листе, удерживая левую кнопку мыши. Автоматически появится окно, где нужно выбрать макрос, который должен запускаться при щелчке по нарисованной кнопке.

Создание пользовательских функций на VBA

Создание пользовательских функций или, как их иногда еще называют, UDF-функций (User Defined Functions) принципиально не отличается от создания макроса в обычном программном модуле. Разница только в том, что макрос выполняет последовательность действий с объектами книги (ячейками, формулами и значениями, листами, диаграммами и т.д.), а пользовательская функция - только с теми значениями, которые мы передадим ей как аргументы (исходные данные для расчета).

Чтобы создать пользовательскую функцию для расчета, например, налога на добавленную стоимость (НДС) откроем редактор VBA, добавим новый модуль через меню Insert - Module и введем туда текст нашей функции:

Обратите внимание, что в отличие от макросов функции имеют заголовок Function вместо Sub и непустой список аргументов (в нашем случае это Summa). После ввода кода наша функция становится доступна в обычном окне Мастера функций (Вставка - Функция) в категории Определенные пользователем (User Defined) :

После выбора функции выделяем ячейки с аргументами (с суммой, для которой надо посчитать НДС) как в случае с обычной функцией:

В программе 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. Для этого следует написать макрос, который безошибочно быстро и автоматически объединит ячейки диапазонов с разным количеством строк для каждого столбца.
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» и нажимаем на кнопку «Выполнить».

Потом снова вручную выделите новый диапазон 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, то объединение строк по столбцам будет выполнено только для первого диапазона.

Читайте также: