Приветствую.. "Нарисовал" в Эксель большую схему.. сейчас возникла необходимость поменять формат некоторых элементов - объединенных ячеек в вертикальные столбики в 8 и 16 ячеек. Как можно их быстро все выделить? Ну или автоматом поменять на другой формат (цвет, заливку поменять и т.п.)
Приветствую.. "Нарисовал" в Эксель большую схему.. сейчас возникла необходимость поменять формат некоторых элементов - объединенных ячеек в вертикальные столбики в 8 и 16 ячеек. Как можно их быстро все выделить? Ну или автоматом поменять на другой формат (цвет, заливку поменять и т.п.)ovechkin1973
Может я не верно задачу описал... со мной такое бывает. Как можно найти на листе объединенные ячейки шириной в 1 столбец и высотой 8 строк (или еще 1 столбец и 16 строк) и выделить их одновременно? чтобы можно было сразу ко всем применить форматирование нужное?
Может я не верно задачу описал... со мной такое бывает. Как можно найти на листе объединенные ячейки шириной в 1 столбец и высотой 8 строк (или еще 1 столбец и 16 строк) и выделить их одновременно? чтобы можно было сразу ко всем применить форматирование нужное?ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
Sub ertert() Dim r As Range, rng As Range Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then ' и высотой 8 строк If r.MergeArea.Rows.Count = 8 Then Set rng = Union(rng, r.MergeArea) End If Next r If rng.Count > 2 Then rng.Select End Sub
[/vba]
Типа такого, наверное: [vba]
Код
Sub ertert() Dim r As Range, rng As Range Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then ' и высотой 8 строк If r.MergeArea.Rows.Count = 8 Then Set rng = Union(rng, r.MergeArea) End If Next r If rng.Count > 2 Then rng.Select End Sub
nilem, Работает, но долго. Хотя вручную на порядки дольше будет и ошибок можно наделать. Я попробовал переделать код на объединенные ячейки высотой 16 ячеек и файл завис.. потому как много таких ячеек. Но я дождусь окончания работы макроса...
nilem, Работает, но долго. Хотя вручную на порядки дольше будет и ошибок можно наделать. Я попробовал переделать код на объединенные ячейки высотой 16 ячеек и файл завис.. потому как много таких ячеек. Но я дождусь окончания работы макроса...ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
Да, долго, он ведь перебирает все ячейки в используемом диапазоне. Нажмите Ctrl+End - выделенная ячейка будет нижний правый угол UsedRange. Попробуйте удалить ненужные строки и столбцы, чтобы уменьшить этот диапазон. Сохраните файл после этого.
Да, долго, он ведь перебирает все ячейки в используемом диапазоне. Нажмите Ctrl+End - выделенная ячейка будет нижний правый угол UsedRange. Попробуйте удалить ненужные строки и столбцы, чтобы уменьшить этот диапазон. Сохраните файл после этого.nilem
Это потому что в Union многократно добавляются повторяющиеся диапазоны - от всех составляющих их единичных ячеек. Union в конечном итоге, вроде даже как, должен уникалить список адресов внутри результирующего диапазона (можно проверить через Range.Address), но ему просто тяжело в процессе добавления повторений. Отсюда и внушительное время выполнения.
Сделал версию с предварительным отбором уникальных адресов с помощью Словаря - отрабатывает за пару-тройку секунд: [vba]
Код
Sub ertert2() Dim r As Range, rng As Range, arr Dim odic As Object, i As Long, addr As String
Set odic = CreateObject("Scripting.Dictionary")
For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then ' и высотой 8 строк If r.MergeArea.Rows.Count = 8 Then addr = r.MergeArea.Address(False, False) If Not odic.Exists(addr) Then odic.Add addr, addr End If End If Next r
arr = odic.Keys Set rng = Range(arr(LBound(arr))) For i = LBound(arr) + 1 To UBound(arr) Set rng = Union(rng, Range(arr(i))) Next i
Это потому что в Union многократно добавляются повторяющиеся диапазоны - от всех составляющих их единичных ячеек. Union в конечном итоге, вроде даже как, должен уникалить список адресов внутри результирующего диапазона (можно проверить через Range.Address), но ему просто тяжело в процессе добавления повторений. Отсюда и внушительное время выполнения.
Сделал версию с предварительным отбором уникальных адресов с помощью Словаря - отрабатывает за пару-тройку секунд: [vba]
Код
Sub ertert2() Dim r As Range, rng As Range, arr Dim odic As Object, i As Long, addr As String
Set odic = CreateObject("Scripting.Dictionary")
For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then ' и высотой 8 строк If r.MergeArea.Rows.Count = 8 Then addr = r.MergeArea.Address(False, False) If Not odic.Exists(addr) Then odic.Add addr, addr End If End If Next r
arr = odic.Keys Set rng = Range(arr(LBound(arr))) For i = LBound(arr) + 1 To UBound(arr) Set rng = Union(rng, Range(arr(i))) Next i
Да, долго, он ведь перебирает все ячейки в используемом диапазоне
nilem, Проверил на работе.. обрабатывает буквально файл за полминуты.. хотя дома просто комп завис (дома комп вроде даже мощнее, чем на работе)ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
Да, можно попробовать убрать лишние Union (хотя, мне кажется, толку мало от этого - вполне себе объединяет, не напрягаясь). Вот попробуйте с выбором кол-ва строк и столбцов и с учетом замечаний Gustav,
[vba]
Код
Sub ertert() Dim r As Range, rng As Range, adr$, rw&, cl&
rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1) ''or 'rw = Range("A1").Value 'cl = Range("B1").Value If rw * cl = 0 Then Exit Sub
Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then With r.MergeArea If (.Rows.Count = rw) * (.Columns.Count = cl) Then If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea) End If End With End If Next r 'Range("A2").Value = rng.Address(0, 0) If rng.Count > 2 Then rng.Select End Sub
[/vba]
Вот эту строчку [vba]
Код
If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea)
[/vba] надо вот так: [vba]
Код
If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea): adr = adr & "," & .Address
[/vba] Забыл
Да, можно попробовать убрать лишние Union (хотя, мне кажется, толку мало от этого - вполне себе объединяет, не напрягаясь). Вот попробуйте с выбором кол-ва строк и столбцов и с учетом замечаний Gustav,
[vba]
Код
Sub ertert() Dim r As Range, rng As Range, adr$, rw&, cl&
rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1) ''or 'rw = Range("A1").Value 'cl = Range("B1").Value If rw * cl = 0 Then Exit Sub
Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then With r.MergeArea If (.Rows.Count = rw) * (.Columns.Count = cl) Then If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea) End If End With End If Next r 'Range("A2").Value = rng.Address(0, 0) If rng.Count > 2 Then rng.Select End Sub
[/vba]
Вот эту строчку [vba]
Код
If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea)
[/vba] надо вот так: [vba]
Код
If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea): adr = adr & "," & .Address
А вчера сколько? При выполнении самой первой редакции макроса из сообщения №3 - да на Вашем боевом файле?
точно не засекал, но дома могу попробовать. Прикручу к файлу таймер, где то в других проектах был... на работе сейчас часами засекал время.. А так примерно несколько минут работал файл на поиске 1*8 объединенных ячееек, а на 1*16 комп завис..
А вчера сколько? При выполнении самой первой редакции макроса из сообщения №3 - да на Вашем боевом файле?
точно не засекал, но дома могу попробовать. Прикручу к файлу таймер, где то в других проектах был... на работе сейчас часами засекал время.. А так примерно несколько минут работал файл на поиске 1*8 объединенных ячееек, а на 1*16 комп завис..ovechkin1973
Плохо когда не знаешь, да еще забудешь. Правильно сформулированный вопрос содержит половину ответа.
С отключенным обновлением работает быстрее. Казалось бы... ?
[vba]
Код
Sub ertert() Dim r As Range, rng As Range, adr$, rw&, cl& Dim tm! rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1) ''or 'rw = Range("A1").Value 'cl = Range("B1").Value If rw * cl = 0 Then Exit Sub Application.ScreenUpdating = False tm = Timer Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then With r.MergeArea If (.Rows.Count = rw) * (.Columns.Count = cl) Then If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea): adr = adr & "," & .Address End If End With End If Next r 'Range("A2").Value = rng.Address(0, 0) If rng.Count > 2 Then rng.Select Application.ScreenUpdating = True MsgBox Timer - tm End Sub
[/vba]
С отключенным обновлением работает быстрее. Казалось бы... ?
[vba]
Код
Sub ertert() Dim r As Range, rng As Range, adr$, rw&, cl& Dim tm! rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1) ''or 'rw = Range("A1").Value 'cl = Range("B1").Value If rw * cl = 0 Then Exit Sub Application.ScreenUpdating = False tm = Timer Set rng = Range("A1") For Each r In ActiveSheet.UsedRange.Cells If r.MergeCells Then With r.MergeArea If (.Rows.Count = rw) * (.Columns.Count = cl) Then If InStr(adr, .Address) = 0 Then Set rng = Union(rng, r.MergeArea): adr = adr & "," & .Address End If End With End If Next r 'Range("A2").Value = rng.Address(0, 0) If rng.Count > 2 Then rng.Select Application.ScreenUpdating = True MsgBox Timer - tm End Sub
Узрел возможность сократить время выполнения за счет отказа от цикла по всем ячейкам в пользу двух вложенных циклов - по строкам и по столбцам - с шагами (внимание!), равными размерам искомых объединенных ячеек. Т.е., фигурально выражаясь, протыкаем (щупаем) копну сена спицей не по каждой строке, а по каждой "восьмой" строке, с гарантированным попаданием хотя бы в одну простую ячейку (а больше и не надо) из состава объединенной ячейки (в терминах "морского боя": подбиваем одну из труб большого корабля с тем, чтобы затем утопить весь корабль).
Продолжаю использовать фрагменты кода nilem, за которые, пользуясь случаем, его великодушно благодарю.
[vba]
Код
Sub ertert3() Dim r As Range, usdr As Range, rng As Range, arr, rw&, cl& Dim odic As Object, i As Long, j As Long, addr As String, tm!
rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1)
If rw * cl = 0 Then Exit Sub Application.ScreenUpdating = False tm = Timer
Set odic = CreateObject("Scripting.Dictionary")
Set usdr = ActiveSheet.UsedRange
For i = 1 To usdr.Rows.Count Step rw For j = 1 To usdr.Columns.Count Step cl With usdr.Cells(i, j) If .MergeCells Then If .MergeArea.Rows.Count = rw And .MergeArea.Columns.Count = cl Then addr = .MergeArea.Address(False, False) If Not odic.Exists(addr) Then odic.Add addr, addr End If End If End With Next j Next i
arr = odic.Keys Set rng = Range(arr(LBound(arr))) For i = LBound(arr) + 1 To UBound(arr) Set rng = Union(rng, Range(arr(i))) Next i
If rng.Count > 2 Then rng.Select Application.ScreenUpdating = True Debug.Print Timer - tm End Sub
[/vba]
Узрел возможность сократить время выполнения за счет отказа от цикла по всем ячейкам в пользу двух вложенных циклов - по строкам и по столбцам - с шагами (внимание!), равными размерам искомых объединенных ячеек. Т.е., фигурально выражаясь, протыкаем (щупаем) копну сена спицей не по каждой строке, а по каждой "восьмой" строке, с гарантированным попаданием хотя бы в одну простую ячейку (а больше и не надо) из состава объединенной ячейки (в терминах "морского боя": подбиваем одну из труб большого корабля с тем, чтобы затем утопить весь корабль).
Продолжаю использовать фрагменты кода nilem, за которые, пользуясь случаем, его великодушно благодарю.
[vba]
Код
Sub ertert3() Dim r As Range, usdr As Range, rng As Range, arr, rw&, cl& Dim odic As Object, i As Long, j As Long, addr As String, tm!
rw = Application.InputBox(Prompt:="Строк в об. ячейке:", Default:=8, Type:=1) cl = Application.InputBox(Prompt:="Столбцов в об. ячейке:", Default:=1, Type:=1)
If rw * cl = 0 Then Exit Sub Application.ScreenUpdating = False tm = Timer
Set odic = CreateObject("Scripting.Dictionary")
Set usdr = ActiveSheet.UsedRange
For i = 1 To usdr.Rows.Count Step rw For j = 1 To usdr.Columns.Count Step cl With usdr.Cells(i, j) If .MergeCells Then If .MergeArea.Rows.Count = rw And .MergeArea.Columns.Count = cl Then addr = .MergeArea.Address(False, False) If Not odic.Exists(addr) Then odic.Add addr, addr End If End If End With Next j Next i
arr = odic.Keys Set rng = Range(arr(LBound(arr))) For i = LBound(arr) + 1 To UBound(arr) Set rng = Union(rng, Range(arr(i))) Next i
If rng.Count > 2 Then rng.Select Application.ScreenUpdating = True Debug.Print Timer - tm End Sub
А вчера сколько? При выполнении самой первой редакции макроса из сообщения №3 - да на Вашем боевом файле?
Проверил макрос из поста №3 - работает на поиске и выделении 8ми ячеек 7,6 секунд примерно, а на поиске 16ти ячеек - 21 секунда. Но каждый запуск почему то дает чуть разное время (плюс-минус секунда примерно). К макросу добавил:
[vba]
Код
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If Application.DisplayStatusBar = False Application.DisplayAlerts = False
А вчера сколько? При выполнении самой первой редакции макроса из сообщения №3 - да на Вашем боевом файле?
Проверил макрос из поста №3 - работает на поиске и выделении 8ми ячеек 7,6 секунд примерно, а на поиске 16ти ячеек - 21 секунда. Но каждый запуск почему то дает чуть разное время (плюс-минус секунда примерно). К макросу добавил:
[vba]
Код
Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False If Workbooks.Count Then ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False End If Application.DisplayStatusBar = False Application.DisplayAlerts = False