Hi Peter,
on the contrary thanks for budding in :)
i'm collecting answers here from multi branches in this thread...
this is off the cuff.. no testing.
do you mean that
a: the selection is incorrect
or
b: that the 'areas' are only 'jumbled'
this seems due to fact that any union or intersect is build from the
ACTIVEcell forwards.. and wraps around at the end.. first selected
cell..alas
afaik no easy way to recreate/reorder a 'hashed' multiarea :(
(hence the threads' title?)
'============================
re other thread Square():
'============================
Peter.. be careful there..
i'had already done some speedtesting.
looping may not look cool.
and all the variables may not look cool either..
you CANNOT depend on the SEQUENCE of multiareas.
using entirerow/column definitely slows it down.
Function rRect2(rng As Range) As Range
Set rRect2 = Intersect(rng.EntireColumn, rng.EntireRow)
End Function
'================================
Re How's this for methodology..
'================================
Significant speed improvement if following change is made to
prevent (slow) reunions when either multia is 4096.
(disproportionate etc :)
change THIS
Set rngT = colRaw(1)
For r = 2 To colRaw.Count
If rngT.Areas.Count + colRaw(r).Areas.Count 8192 Then
SegmentedCells.Add rngT
Set rngT = colRaw(r)
Else
Set rngT = Union(rngT, colRaw(r))
End If
Next
SegmentedCells.Add rngT
to THIS:
Set rngT = colRaw(1)
For r = 2 To colRaw.Count
If rngT.Areas.Count + colRaw(r).Areas.Count m \ 2 Then
SegmentedCells.Add rngT
Set rngT = colRaw(r)
Else
Set rngT = Union(rngT, colRaw(r))
End If
Next
SegmentedCells.Add rngT
i've got the feeling we'll be back :)
--
keepITcool
|
www.XLsupport.com | keepITcool chello nl | amsterdam
Peter T wrote :
Hi KeepITCool
Hope you don't mind my squeezing into your thread.
Really like the CF alternative, gives extra possibilities.
A couple of things with the DV collection if "Goto useDV".
Similar as I mentioned to Norman, in xl97 would need to
change:
With itm(0).Validation
.Add itm(1), itm(2), itm(3), itm(4), itm(5)
to
.Add itm(1), Abs(itm(2)), itm(3), itm(4), itm(5)
The DV Collection correctly replaces, but when doing this:
ActiveCell.SpecialCells(xlCellTypeAllValidation).S elect
I get a perfect jigsaw of areas, rather than the single
area of DV I had originally applied over everything. Using
the array method, when done I end up with the original
single area of DV.
Regards,
Peter
Function Inverse(rngA As Range, Optional bUsedRange As
Boolean, _
'code
If colDV.Count 0 Then
For Each itm In colDV
With itm(0).Validation
.Add itm(1), itm(2), itm(3), itm(4), itm(5)
.IgnoreBlank = itm(6)
.InCellDropdown = itm(7)
.ShowError = itm(8)
.ErrorTitle = itm(9)
.ErrorMessage = itm(10)
.ShowInput = itm(11)
.InputTitle = itm(12)
.InputMessage = itm(13)
End With
Next
End If
'code
End Function