Excel vba объединена ли ячейка
Добрый день!
Подскажите, пожалуйста, каким образом можно искать объединённые ячейки (или проверить, есть ли объединённые ячейки) и, допустим, выделить все объединённые ячейки?
Добрый день!
Подскажите, пожалуйста, каким образом можно искать объединённые ячейки (или проверить, есть ли объединённые ячейки) и, допустим, выделить все объединённые ячейки? Roman777
[p.s.]не, так он их все объединит.
[p.s.]не, так он их все объединит. KSV
[p.s.]не, так он их все объединит. Автор - KSV
Дата добавления - 02.06.2015 в 10:18
Sub Макрос1()
Dim r As Range, r1 As Range, s$, b As Boolean
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then s = s & r.Address & "|"
Next
If Len(s) > 0 Then
s = Left(s, Len(s) - 1)
Range(Join(Split(s, "|"), " ,")).Select
End If
End Sub
[/vba]
Выделит все обедененные ячейки в выделенном диапазоне
ЗЫ
Для правильности - нужно бы использовать словарь с добавлением "mergearea"(файл 2):
[vba]
Sub Макрос2()
Dim r As Range, r1 As Range, s$, b As Boolean, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then If Not dic.Exists(r.MergeArea.Address) Then dic.Add r.MergeArea.Address, r.MergeArea.Address
If b Then s = s & r.Address & "|"
Next
If dic.Count > 0 Then
Range(Join(dic.keys, " ,")).Select
End If
End Sub
Sub Макрос1()
Dim r As Range, r1 As Range, s$, b As Boolean
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then s = s & r.Address & "|"
Next
If Len(s) > 0 Then
s = Left(s, Len(s) - 1)
Range(Join(Split(s, "|"), " ,")).Select
End If
End Sub
[/vba]
Выделит все обедененные ячейки в выделенном диапазоне
ЗЫ
Для правильности - нужно бы использовать словарь с добавлением "mergearea"(файл 2):
[vba]
Sub Макрос2()
Dim r As Range, r1 As Range, s$, b As Boolean, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then If Not dic.Exists(r.MergeArea.Address) Then dic.Add r.MergeArea.Address, r.MergeArea.Address
If b Then s = s & r.Address & "|"
Next
If dic.Count > 0 Then
Range(Join(dic.keys, " ,")).Select
End If
End Sub
Иногда все проще чем кажется с первого взгляда.
Sub Макрос1()
Dim r As Range, r1 As Range, s$, b As Boolean
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then s = s & r.Address & "|"
Next
If Len(s) > 0 Then
s = Left(s, Len(s) - 1)
Range(Join(Split(s, "|"), " ,")).Select
End If
End Sub
[/vba]
Выделит все обедененные ячейки в выделенном диапазоне
ЗЫ
Для правильности - нужно бы использовать словарь с добавлением "mergearea"(файл 2):
[vba]
Sub Макрос2()
Dim r As Range, r1 As Range, s$, b As Boolean, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
Set r1 = Selection
For Each r In r1
b = r.MergeCells
If b Then If Not dic.Exists(r.MergeArea.Address) Then dic.Add r.MergeArea.Address, r.MergeArea.Address
If b Then s = s & r.Address & "|"
Next
If dic.Count > 0 Then
Range(Join(dic.keys, " ,")).Select
End If
End Sub
Объект Range в VBA Excel представляет диапазон ячеек. Он (объект Range) может описывать любой диапазон, начиная от одной ячейки и заканчивая сразу всеми ячейками рабочего листа.
- Одна ячейка – Range("A1") .
- Девять ячеек – Range("A1:С3") .
- Весь рабочий лист в Excel 2016 – Range("1:1048576") .
Для справки: выражение Range("1:1048576") описывает диапазон с 1 по 1048576 строку, где число 1048576 – это номер последней строки на рабочем листе Excel 2016.
В VBA Excel есть свойство Cells объекта Range, которое позволяет обратиться к одной ячейке в указанном диапазоне (возвращает объект Range в виде одной ячейки). Если в коде используется свойство Cells без указания диапазона, значит оно относится ко всему диапазону активного рабочего листа.
Примеры обращения к одной ячейке:
- Cells(1000) , где 1000 – порядковый номер ячейки на рабочем листе, возвращает ячейку «ALL1».
- Cells(50, 20) , где 50 – номер строки рабочего листа, а 20 – номер столбца, возвращает ячейку «T50».
- Range("A1:C3").Cells(6) , где «A1:C3» – заданный диапазон, а 6 – порядковый номер ячейки в этом диапазоне, возвращает ячейку «C2».
Для справки: порядковый номер ячейки в диапазоне считается построчно слева направо с перемещением к следующей строке сверху вниз.
Подробнее о том, как обратиться к ячейке, смотрите в статье: Ячейки (обращение, запись, чтение, очистка).
В этой статье мы рассмотрим свойства объекта Range, применимые, в том числе, к диапазону, состоящему из одной ячейки.
Еще надо добавить, что свойства и методы объектов отделяются от объектов точкой, как в третьем примере обращения к одной ячейке: Range("A1:C3").Cells(6) .
Свойства ячейки (объекта Range)
Свойство | Описание |
---|---|
Address | Возвращает адрес ячейки (диапазона). |
Borders | Возвращает коллекцию Borders, представляющую границы ячейки (диапазона). Подробнее… |
Cells | Возвращает объект Range, представляющий коллекцию всех ячеек заданного диапазона. Указав номер строки и номер столбца или порядковый номер ячейки в диапазоне, мы получаем конкретную ячейку. Подробнее… |
Characters | Возвращает подстроку в размере указанного количества символов из текста, содержащегося в ячейке. Подробнее… |
Column | Возвращает номер столбца ячейки (первого столбца диапазона). Подробнее… |
ColumnWidth | Возвращает или задает ширину ячейки в пунктах (ширину всех столбцов в указанном диапазоне). |
Comment | Возвращает комментарий, связанный с ячейкой (с левой верхней ячейкой диапазона). |
CurrentRegion | Возвращает прямоугольный диапазон, ограниченный пустыми строками и столбцами. Очень полезное свойство для возвращения рабочей таблицы, а также определения номера последней заполненной строки. |
EntireColumn | Возвращает весь столбец (столбцы), в котором содержится ячейка (диапазон). Диапазон может содержаться и в одном столбце, например, Range("A1:A20") . |
EntireRow | Возвращает всю строку (строки), в которой содержится ячейка (диапазон). Диапазон может содержаться и в одной строке, например, Range("A2:H2") . |
Font | Возвращает объект Font, представляющий шрифт указанного объекта. Подробнее о цвете шрифта… | HorizontalAlignment | Возвращает или задает значение горизонтального выравнивания содержимого ячейки (диапазона). Подробнее… |
Interior | Возвращает объект Interior, представляющий внутреннюю область ячейки (диапазона). Применяется, главным образом, для возвращения или назначения цвета заливки (фона) ячейки (диапазона). Подробнее… |
Name | Возвращает или задает имя ячейки (диапазона). |
NumberFormat | Возвращает или задает код числового формата для ячейки (диапазона). Примеры кодов числовых форматов можно посмотреть, открыв для любой ячейки на рабочем листе Excel диалоговое окно «Формат ячеек», на вкладке «(все форматы)». Свойство NumberFormat диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковый числовой формат. Если нужно присвоить ячейке текстовый формат, записывается так: Range("A1").NumberFormat = "@" . |
Offset | Возвращает объект Range, смещенный относительно первоначального диапазона на указанное количество строк и столбцов. Подробнее… |
Resize | Изменяет размер первоначального диапазона до указанного количества строк и столбцов. Строки добавляются или удаляются снизу, столбцы – справа. Подробнее… |
Row | Возвращает номер строки ячейки (первой строки диапазона). Подробнее… |
RowHeight | Возвращает или задает высоту ячейки в пунктах (высоту всех строк в указанном диапазоне). |
Text | Возвращает форматированный текст, содержащийся в ячейке. Свойство Text диапазона возвращает значение NULL, за исключением тех случаев, когда все ячейки в диапазоне имеют одинаковое содержимое и один формат. Предназначено только для чтения. |
Value | Возвращает или задает значение ячейки, в том числе с отображением значений в формате Currency и Date. Тип данных Variant. Value является свойством ячейки по умолчанию, поэтому в коде его можно не указывать. |
Value2 | Возвращает или задает значение ячейки. Тип данных Variant. Значения в формате Currency и Date будут отображены в виде чисел с типом данных Double. | VerticalAlignment | Возвращает или задает значение вертикального выравнивания содержимого ячейки (диапазона). Подробнее… |
В таблице представлены не все свойства объекта Range. С полным списком вы можете ознакомиться не сайте разработчика.
Простые примеры для начинающих
Вы можете скопировать примеры кода VBA Excel в стандартный модуль и запустить их на выполнение. Как создать стандартный модуль и запустить процедуру на выполнение, смотрите в статье VBA Excel. Начинаем программировать с нуля.
Учтите, что в одном программном модуле у всех процедур должны быть разные имена. Если вы уже копировали в модуль подпрограммы с именами Primer1, Primer2 и т.д., удалите их или создайте еще один стандартный модуль.
Форматирование ячеек
Заливка ячейки фоном, изменение высоты строки, запись в ячейки текста, автоподбор ширины столбца, выравнивание текста в ячейке и выделение его цветом, добавление границ к ячейкам, очистка содержимого и форматирования ячеек.
Если вы запустите эту процедуру, информационное окно MsgBox будет прерывать выполнение программы и сообщать о том, что произойдет дальше, после его закрытия.
содержимое листа:
Ссылка на файл с макросом ge.tt/849QWKC2/v/0?c
- Вопрос задан более трёх лет назад
- 3043 просмотра
Потому что VBA криво работает с merge cells, и на официальном сайте экселя не рекомендуют использовать объединенные ячейки, если вы разрабатываете что-то сложное на VBA, потому что это нарушает и сортировку и цикл по ячейкам.
Можно конечно придумать костыли перебором ячеек в цикле, но большинство готовых функций обработки столбцов и рядов работать не будут.
Замените SearchOrder:=xlByColumns на SearchOrder:=xlByRows
и удалите строки Cells.FindNext(After:=ActiveCell).Activate
Макрорекодер (единственно, что разбирают на всяких курсах по VBA) - это реальная жесть ;) Нужно после него обязательно разбирать весь код и смотреть, что он делает. Чтобы нормально писать макросы, нужно читать мануал (благо в справке 80-90% документировано). Успехов!
Можно еще порекомендовать не мерджить ячейки, т.к. это приводит к массе проблем в самых разных контекстах. Если нужно выровнять текст по центру через несколько ячеек, всегда можно использовать функцию Center Across Selection \ Горизонтальное выравнивание - по центру выделения. Визуально эффект тот же без глюков и недоразумений. С вертикалью сложнее, но всегда можно что-нибудь придумать.
John Smith: Я только за, но дело в том, что я делаю генерацию отчета, и если ячейки не мержить, то получается не так красиво. Например нужно вывести Количество: [Count], Сумма: [TotalPrice] , если все это в одной смерженной ячейке, то после замены плейсхолдеров между словами будет точно один пробел, а если разнести по ячейкам, то будет пустое пространство, плюс если текст длинный в какой то ячейке, то текст перекроется соседними ячейками
asdz: Я бы порекомендовал сначала заполнить ячейки отчёта, а в конце нужные объединить. Длинные строки опять же плохо - при печати текст может вылезать за рамки печати. Может есть смысл выгружать данные в Word (надо быть готовым, что структура объектов там сложнее).
Дѣаволъ: но ведь если я объединю несколько ячеек, текст останется только от первой? С вордом тоже можно, я все равно с объектами не буду работать, только с документом - для поиска и замены плейсхолдеров.
asdz: Да, только от первой. Либо нужно писать функцию, которая сначала прочитает значения, затем после объединения восстановит. Или можно сдвинуть сетку в квадраты, но могут быть проблемы с печатью на других ПК; также не очень удобно что-то изменять. Поэтому форму лучше продумать заранее.
Либо ничего не объединять вообще, как я писал. Например, вместо того, чтобы мерджить две ячейки (напрмер, A1+B1) и выводить в них упомянутый вами "Количество: [Count], Сумма: [TotalPrice]", можно вывести эту же строку в A1 и сделать Range("A1:B1").HorizontalAlignment = xlCenterAcrossSelection. Единственный минус - если не нужна центровка, придется извращаться.
Надо произвести поиск по колонкам, то есть по столбцам.
ub Прейре_к_запису()
' Макрос Прейре к запису
Dim nomdoma As Object
Dim a As Object
Dim b As Object
Dim ws As Worksheet
Dim c As Object
Dim d As Object
Dim e As Integer
Set a = Workbooks(1).Worksheets(1).Range("E18")
Set b = Workbooks(1).Worksheets(1).Range("E19")
If a.Value = 12 Then
Set ws = dom12
ElseIf a.Value = 13 Then
Set ws = dom13
ElseIf a.Value = 16 Then
Set ws = dom16
ElseIf a.Value = 18 Then
Set ws = dom18
ElseIf a.Value = 20 Then
Set ws = dom20
Else
MsgBox "Нет дом с токой номером!"
Exit Sub
End If
e = ws.index ' е преровняется к индексу рабочего листа'
If ws.index = dom16.index And b >= 49 Then 'MsgBox dom16.Type Обрашаемся к типу названного листа и указивем в условие if
MsgBox "В доме " & a.Value & " с номером " & _
b & " кватира не сушестьвует. Имеется 48кв! Вибирите другой номер квартиру! Ниже 49", vbAbortRetryIgnore
Exit Sub
End If
Set nomdoma = ws.Range("H:H").Find(What:=b.Value, LookIn:=xlValues, _
lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
ThisWorkbook.Worksheets(e).Select
Range(nomdoma.Address).Offset(rowOffset:=0, columnOffset:=-7).Select
End Sub
Используйте функцию Range("A:A").Find
= Мир MS Excel/Объединение ячеек - Мир MS Excel
Войти через uID
Войти через uID
На форуме есть макросы MergePlus и ReMerge
Задача немного глобальней. Файл, с идущими подряд строками данных (допустим, подряд три строки, 5 столбцов). В каждой строке по горизонтали нужно проверить, является ли ячейка объединенной (2 строки, 1 ячейка по столбцу объединяется в двух строках) и тогда ее пропускаем, а если нет - то их объединить.
Например, ячейка A1 объединяется с A2, B1 с B2 и так в каждой строке по всей ее длине. Длина строк фиксирована (сорри, что не приложил пример, то же самое на словах, нет смысла). Проблема не в том, чтобы объединить, а чтобы данные в идущих подряд строках не объединять. Вставить пустые строки и объединить с пустыми непустые..
В случае трех строк вопрос простой, в случае 2053 гораздо сложнее. Подскажите, пожалуйста.
На форуме есть макросы MergePlus и ReMerge
Задача немного глобальней. Файл, с идущими подряд строками данных (допустим, подряд три строки, 5 столбцов). В каждой строке по горизонтали нужно проверить, является ли ячейка объединенной (2 строки, 1 ячейка по столбцу объединяется в двух строках) и тогда ее пропускаем, а если нет - то их объединить.
Например, ячейка A1 объединяется с A2, B1 с B2 и так в каждой строке по всей ее длине. Длина строк фиксирована (сорри, что не приложил пример, то же самое на словах, нет смысла). Проблема не в том, чтобы объединить, а чтобы данные в идущих подряд строках не объединять. Вставить пустые строки и объединить с пустыми непустые..
В случае трех строк вопрос простой, в случае 2053 гораздо сложнее. Подскажите, пожалуйста. w00t
Задача немного глобальней. Файл, с идущими подряд строками данных (допустим, подряд три строки, 5 столбцов). В каждой строке по горизонтали нужно проверить, является ли ячейка объединенной (2 строки, 1 ячейка по столбцу объединяется в двух строках) и тогда ее пропускаем, а если нет - то их объединить.
Например, ячейка A1 объединяется с A2, B1 с B2 и так в каждой строке по всей ее длине. Длина строк фиксирована (сорри, что не приложил пример, то же самое на словах, нет смысла). Проблема не в том, чтобы объединить, а чтобы данные в идущих подряд строках не объединять. Вставить пустые строки и объединить с пустыми непустые..
В случае трех строк вопрос простой, в случае 2053 гораздо сложнее. Подскажите, пожалуйста. Автор - w00t
Дата добавления - 05.11.2013 в 22:18
Вы знаете, как найти и выделить все объединенные ячейки в Excel? Вот крутые хитрые способы быстро определить и выбрать все объединенные ячейки в выделении или диапазоне в Microsoft Excel 2007 и 2010.
Определите и выберите все объединенные ячейки с помощью команды Найти
Вы можете определить и выбрать все объединенные ячейки на активном листе с помощью Найдите команду со следующими шагами:
1, Нажмите Главная > Найти и выбрать > Найдите для открытия Найти и заменить диалоговое окно. Вы также можете открыть Найти и заменить диалоговое окно с нажатием Ctrl + F ключи.
2, Нажмите Формат в диалоговом окне, (Если вы не можете узнать Формат кнопку, нажмите Доступные опции кнопку, чтобы развернуть диалоговое окно.) см. снимок экрана:
3. В всплывающем Найти формат диалоговое окно, только отметьте Объединить ячейки вариант в Текстовый контроль Раздел под центровка вкладку и щелкните OK.
4. Теперь вы вернетесь к Найти и заменить диалоговое окно, нажмите Найти все кнопка. Все объединенные ячейки перечислены в нижней части этого диалогового окна. Выберите все результаты поиска, удерживая Shift ключ.
Теперь при выборе всех результатов поиска выделяются все объединенные ячейки на активном листе. Смотрите скриншот:
Советы: Если вы хотите только идентифицировать, находить и выбирать объединенные ячейки в выделенном фрагменте, вам необходимо сначала выбрать диапазон.
Определите все объединенные ячейки с кодом VBA
VBA 1: определить и выделить все объединенные ячейки
1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, и вставьте следующий макрос в Модули Окно.
3, нажмите F5 ключ для запуска этого макроса. Все объединенные ячейки в активном листе идентифицируются и выделяются, см. Снимок экрана:
VBA 2: определить и перечислить все объединенные ячейки
1. Удерживайте ALT + F11 ключи, и он открывает Microsoft Visual Basic для приложений окно.
2. Нажмите Вставить > Модули, и вставьте следующий макрос в Модули Окно.
3, нажмите F5 Нажмите клавишу для запуска этого макроса, все объединенные ячейки будут перечислены во всплывающем диалоговом окне. Смотрите скриншот:
Выберите и подсчитайте все объединенные ячейки с помощью Kutools for Excel
Kutools for Excel's Выбрать объединенные ячейки Инструмент поможет вам идентифицировать, найти и выбрать все объединенные ячейки в выделении одним щелчком мыши.
1. Выберите диапазон данных, в котором вы хотите выделить объединенные ячейки.
2. Нажмите Кутулс > Выберите > Выбрать объединенные ячейки, см. снимок экрана:
3. И все объединенные ячейки в выделении были выбраны сразу, и количество объединенных ячеек также подсчитывается, см. Снимок экрана:
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . -->
- Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
У меня есть Excel 12.3.6 для Mac, и он часто берет на себя объединение ячеек. Тогда я не могу сортировать, не идентифицируя и не объединяя каждую объединенную ячейку. В большой таблице это нецелесообразно. Должна быть возможность использовать кнопку «Объединить» на панели инструментов, чтобы разъединить все ячейки в выбранном диапазоне, а не по одной за раз! У кого-то еще есть такая проблема? или еще лучше, решение?
Читайте также: