Есть диапазон, состоящий из нескольких областей Адрес диапазона (адреса всех областей через запятую) длиннее 255 символов (я так понял, что это и есть ограничение)
Вопрос: как получить этот длинный адрес без цикла по всем областям с накоплением адресов. То есть можно ли хоть как-то ускорить этот процесс?
Код
Код
Option Explicit
Option Private Module
'====================================================================================================
Sub test()
Dim rng As Range
Dim x, tx$, t!, a&
Set rng = Intersect(Columns(1), ActiveSheet.UsedRange).SpecialCells(xlCellTypeVisible)
tx = rng.Address(, , xlR1C1)
x = Split(tx, ",")
Range("D1").End(xlToRight).ClearContents
Range("D1").Resize(1, UBound(x) + 1).Value = x
MsgBox "SHORT: " & Len(tx) & " — " & UBound(x) + 1
If Len(tx) < 200 Then Exit Sub
t = Timer
ReDim x(rng.Areas.Count - 1)
For a = 1 To rng.Areas.Count
x(a - 1) = rng.Areas(a).Address(, , xlR1C1)
Next a
MsgBox "Full: " & Len(Join(x, ",")) & " — " & UBound(x) + 1, vbInformation, Format(1000 * (Timer - t), "0 ms")
End Sub
'====================================================================================================
Скрины
файл большой, так что пока без него
Пока так: функция для получения ПОЛНОГО адреса переданного диапазона циклом по областям
Код
'====================================================================================================
Function PRDX_RangeAddress(rng As Range, Optional RefStyle&, Optional ToArray As Boolean)
Dim arr() As String, tx$, a&, flag As Boolean
Dim fRF1 As Boolean, fRF2 As Boolean
If RefStyle = 0 Then
RefStyle = xlA1
ElseIf RefStyle = xlR1C1 Then
fRF1 = True: fRF2 = True
ElseIf RefStyle <> xlA1 Then
MsgBox "Bad RefStyle «" & RefStyle & "»", vbCritical, "RangeAddress"
Err.Raise xlErrNA: Exit Function
End If
tx = rng.Address(fRF1, fRF2, RefStyle)
If Len(tx) < 220 Then GoTo fin
ReDim arr(rng.Areas.Count - 1): flag = True
For a = 1 To rng.Areas.Count
arr(a - 1) = rng.Areas(a).Address(fRF1, fRF2, RefStyle)
Next a
fin:
If ToArray Then
If flag Then PRDX_RangeAddress = arr: Exit Function
PRDX_RangeAddress = Split(tx, ",")
Else
If flag Then PRDX_RangeAddress = Join(arr, ","): Exit Function
PRDX_RangeAddress = tx
End If
End Function
'====================================================================================================
получение адреса диапазона, состоящего из 400 тыс. отдельных ячеек занимает меньше 1 секунды (см. скрин выше)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
БМВ, Миш, интересен сам подход, но цель тоже есть - потом можно быстро регулярками извлечь из этого адреса все номера строк и столбцов для дальнейших действий (например, определения координат крайних ячеек или получение полного списка задействованных строк и столбцов)
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Сам гугли - я его очень хорошо знаю Помимо комментария Миши, скажу, что работа с адресом (строкой) намного быстрее, чем с ячейками (особенно, если нужно получить список строк/столбцов), поэтому смысл в теме точно есть
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Я извиняюсь что влез, но может решение подскажет реализация от ZVI. У меня файлик завалялся:) Надстройка по выделению ячеек с заданым цветом или цветом шрифта на листе. Если чё, в этом шаманстве ничего не понимаю. Упс, даже зипованная версия 3 не лезет со всеми файлами пароль 123
а сами как думаете? Мне нужен "полный адрес диапазона, состоящего из множества областей", а вы мне выводите в сообщение адрес объединения активной ячейки и её строки/столбца (что само по себе уже глупость, т.к. на выходе будет просто строка/столбец)
Igor67, спасибо. Если я правильно понял, то Владимир делает тоже самое, что и я - собирает в цикле адрес ,что и позволяет сильно ускориться, ТОЛЬКО: • вместо сбора в массив, собирает в строку (быстрее, чем обычно, но выигрыш с Join массива далеко не гарантирован) • получает диапазоны на определённой длине строки, чтобы не выпасть в ошибку - я решил эту проблему итоговой обработкой строки
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous: интересен сам подход, но цель тоже есть - потом можно быстро регулярками извлечь из этого адреса все номера строк и столбцов для дальнейших действий (например, ... или получение полного списка задействованных строк и столбцов)
если выделить несмежные диапазоны A1:D9, E10:G10, K1:L1, мои коды выдадут A:G, K:L и 1:10 то есть, в выделенном несмежном диапазоне задействованы строки 1:10, и задействованы столбцы A:G, K:L Union, как вы знаете, превратит 1:10, 1:10, 1:1 в 1:10
Может быть, я неправильно понял ваш вопрос, но поаккуратнее пожалуйста с:
Бахтиёр: получение полного списка задействованных строк и столбцов
это ОДНО ИЗ ВОЗМОЖНЫХ ПРИМЕНЕНИЙ (медведь - подстава), а тема о СОВЕРШЕННО ДРУГОМ 2.
Цитата
Бахтиёр: если выделить несмежные диапазоны A1:D9, E10:G10, K1:L1, мои коды выдадут A:G, K:L и 1:10
ок - не глупость (сорян - потропился), да - выдают, но уже при тысячах отдельных ячеек Excel виснет навсегда, а моя функция получит адрес на 400 тыс меньше, чем за секунду + ещё одна секунда нужна регуляркам, чтобы достать все номера строк или столбцов + ещё одна секунда нужна, чтобы преобразовать диапазоны типа "1:5" в списки 1, 2, 3, 4 и 5 И вот кому этот метод теперь нужен? Не мне …
Цитата
БМВ: предложение Бахтиёр , решает вопрос определения крайних ячеек
вот ты сам начал спрашивать "истинные цели" и теперь всех запутал. Плохой медведь. Тема о чём? Все варианты тут будем обсуждать??? Это уже не говоря о том, что метод - долгий. Думаешь, я таким макаром не могу в цикле пройтись по всем ячейкам, определяя максимумы и минимумы среди строк и столбцов??? Мне нужно сделать это максимально быстро
ТЕМА О БЫСТРОМ СБОРЕ ПОЛНОГО АДРЕСА ДИАПАЗОНА (более 255 символов), СОСТОЯЩЕГО ИЗ МНОЖЕСТВА ОБЛАСТЕЙ
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
bedvit: Это неверно, он выдает Areas слева-направо и далее сверху-вниз по рабочему листу
смотри: если отдельные области получены из одной с помощью методов выделения типа "только видимые", "только значения/формулы", то да - полный порядок, но при реально ручном выделении будет ровно тот порядок, который был при выделении, в том числе диапазоны, выделенные несколько раз
Угадай порядок выделения, зная MsgBox Selection.Address(0, 0)
зачем так голословно и однозначно утверждать, если проверить можно за 5 сек…
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: зачем так голословно и однозначно утверждать, если проверить можно за 5 сек…
если это в мой адрес, то мимо. Обычно, я проверяю что пишу. Реальный пример
Код
Sub test()
Dim v As Range, s
Range("a1, f3, d1").Select
Selection = 1
Set s = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23), Selection)
Debug.Print s.Address
For Each v In s.Areas
Debug.Print v.Address
Next v
End Sub
могут выделить весь лист, все строки, все колонки. Кто-то пользуется такими диапазонами? обрабатывая тысячи лишних строк? Лично я всегда обрабатываю выделенный пользователем диапазон, кодом описанным выше, там порядок, тот, который я описал.
представь себе, что иногда заранее неизвестно, какого рода диапазон выделит пользователь и/или его не нужно обрабатывать подобными методами
да и к тому же попробуй вот это (данные есть в диапазоне)
Код
Sub tt()
Dim rng As Range
Set rng = Range("D1:F1,A1:B1")
Debug.Print 1, rng.Address(0, 0)
Debug.Print 2, rng.SpecialCells(xlCellTypeConstants, 23).Address(0, 0)
End Sub
- адреса и их порядок не изменятся
Может хватит уже софистикой заниматься и игрой в "а что, если" - вопрос-то про получение адреса
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: Говоришь в общем про области, а подразумеваешь предварительную обработку
здесь согласен. Если брать не обработанный диапазон, то БМВ, Михаил прав.
Цитата
Jack Famous написал: заранее неизвестно, какого рода диапазон выделит пользователь
для этого есть разные SpecialCells, на любой вкус (обычно этого хватает). Я написал из практики (поправлюсь: очередность при обработке диапазона через Intersect(SpecialCells, Selection))
Sub test()
Dim v As Range, s
Range("a1:a3, f3:g10, d1:f5").Select
Selection = 1
Set s = Intersect(ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, 23), Selection)
For Each v In s.Areas
Debug.Print v.Address
Next v
End Sub
$D$1:$F$2 $A$1:$A$3 $D$3:$E$5 $F$3:$G$10
Видимо в соответствии с парадигмой, что сам лист рассчитывается (ячейки) с верхней левой и построчно, слава - направо, сверху-вниз. Как видно, чем ниже строка, тем дальше диапазон (ареал) в списке.
bedvit, я не знаю, зачем ты на медведя наехал А так, если в целом про тему, в которой по делу никому, видимо, нечего предложить, то, по-моему, проще написать функцию, которая максимально быстро обработает диапазон, как надо, чем надеяться, что нужды так обработать диапазон никогда не возникнет
Адреса я много где использую и мне это точно нужно, а то, что привёл навскидку пример, который, ВОЗМОЖНО, можно решить проще/быстрее, так это медведя пинайте — он спросил и это НЕ ПО ТЕМЕ
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
«Только ситхи всё возводят в абсолют» Оби-Ван Кеноби
В этот раз точно ты, но это не страшно — тебе всё-равно замечания не делают Я-то бы сразу по шапке схлопотал, начни такой оффтоп, а тут больше 20ти сообщений, НИ ОДНОГО — по теме и ничья попа (даже мохнатая) ещё не в огне
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous написал: я не знаю, зачем ты на медведя наехал
даже и не думал, что знал - сказал, оказывается - это частность. Ну и ОК, новая инфа по теме всегда хорошо. И вообще, сегодня крутой враппер напилил для работы с СОМ и API C в Excel, с авто управлением памятью и обработкой ошибок. Теперь вместо 5 строк, одна строка кода, почти как в VBA и синтаксис похож. Красота, а поделится не с кем. Может тему в курилке сделать, форум то по Excel :) А по теме: ты бы написал конечную задачу, а то может мы изобретаем мега крутой ... Костыль! Строка в VBA - это медленно. Я в свое время наизобретал костылей, кто бы посоветовал...
А медведь все еще гадает для чего это нужно. Мне кажется что универсальность идет против производительности.Может Union и не быстр но вариант Бахтиёр работает. Зачем текстовый список - пока не придумал.
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
If RefStyle = 0 Then RefStyle = xlA1
If RefStyle = xlR1C1 Then
fRF1 = True: fRF2 = True
ElseIf RefStyle <> xlA1 Then
MsgBox "Bad RefStyle «" & RefStyle & "»", vbCritical, "RangeAddress"
Err.Raise xlErrNA: Exit Function
End If