Home |
Search |
Today's Posts |
#41
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
KeepITcool,
Partial diagnosis: DV is being restored twice in overlapping areas, at least that's what's occuring in my testing. Work with something manageable and look at: If colDV.Count 0 Then For Each itm In colDV Debug.Print itm(0).Address, itm(0).Count, _ itm(0).Areas.Count or compare [count] with [sum areas.count] This is in "Invert" pre "rSqu" amendment, and similar in the amendment with vitm(0). I say "partial" because there's also something else going on, I think all easily fixed but far too late tonight. regards, Peter Following HAS happened: due to editing or while testing I apparently damaged/copied not removed some dummy validation Funny you should mention that. I've experienced similar but ignored. Here's something else: I have a recorded macro to replace identical DV to a single area over everything before testing. But occasionally it errors and I need to run the line ".Delete" (DV) twice. I've got a feeling similar has occurred in proper code without my knowing. I'm only testing with small ranges - visible on the screen, shouldn't need DoEvents. If you can re-produce your "HAS", try adding a second .Delete line before applying any DV. Regards, Peter . |
#42
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
get the the inverse of intersect
For your amusement... Sub Test() NotIntersect(Selection, Application.InputBox("", , , , , , , 8)).Select End Sub Function NotIntersect(rng As Range, x As Range) As Range ' copyright 2001-2004 Jim Wilcox Dim y As Range On Error Resume Next If rng.Parent Is x.Parent Then With x Set y = myUnion(y, Range(Rows(1), .Rows(0))) Set y = myUnion(y, Range(Rows(Rows.Count), .Rows(.Rows.Count + 1))) Set y = Intersect(y, .EntireColumn) Set y = myUnion(y, Range(Columns(1), .Columns(0))) Set y = myUnion(y, _ Range(Columns(Columns.Count), .Columns(.Columns.Count + 1))) Set y = Intersect(y, rng) End With Set NotIntersect = y End If On Error GoTo 0 End Function Private Function myUnion(o As Range, rng As Range) As Range On Error Resume Next If o Is Nothing Then Set myUnion = rng ElseIf rng Is Nothing Then Set myUnion = o Else Set myUnion = Union(o, rng) End If On Error GoTo 0 End Function -Jim (see Organization field to figure out email address) |
#43
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Hi Jim,
I suspect KeepITcool is doing the sensible thing and gone on holiday, hence a reply from me. This looks interesting but I cannot get it to work correctly, a dot or two out of place perhaps. I had a go with something similar, didn't pursue when KeepITcool warned me off the idea (with large ranges). As time's gone by a quick recap - To get a pure inverted range, eg Set RngA = Range("b2:c3,f3:g4") Set RngB = Range("b2:g4") 'square or outer RngA Set rInverted = rFunc(RngA, RngB) Debug.? rInverted.Address $B$4:$C$4,$D$2:$E$4,$F$2:$G$2 For my, and I think Norman's purposes, to subtract ranges whose "outer" areas may only partially intersect. Also, either/both ranges could be single or multiple. The functions in this thread are set up to subtract Intersect (RngA,RngB) from Union(RngA,RngB), but easily adapted to subtract whatever. Eg: Set RngA = Range("B2:C3,F3:G4") Set RngB = Range("B3:B5,C3:G3") Set rSubtracted = rFunc(RngA, RngB) ' subtract Intersect from Union Debug.? rSubtracted.address $F$4:$G$4,$B$4:$B$5,$B$2:$C$2,$D$3:$E$3 I would expect your code to be slower than the methods discussed, but good for smaller ranges to avoid using DV or CF. As it stands it does not appear to return the non- intersecting areas of the ranges thrown at it. I'm hoping I've missed something obvious and looking forward to one of those Doh moments :) Regards, Peter -----Original Message----- get the the inverse of intersect For your amusement... Sub Test() NotIntersect(Selection, Application.InputBox ("", , , , , , , 8)).Select End Sub Function NotIntersect(rng As Range, x As Range) As Range ' copyright 2001-2004 Jim Wilcox Dim y As Range On Error Resume Next If rng.Parent Is x.Parent Then With x Set y = myUnion(y, Range(Rows(1), .Rows(0))) Set y = myUnion(y, Range(Rows(Rows.Count), .Rows (.Rows.Count + 1))) Set y = Intersect(y, .EntireColumn) Set y = myUnion(y, Range(Columns(1), .Columns(0))) Set y = myUnion(y, _ Range(Columns(Columns.Count), .Columns (.Columns.Count + 1))) Set y = Intersect(y, rng) End With Set NotIntersect = y End If On Error GoTo 0 End Function Private Function myUnion(o As Range, rng As Range) As Range On Error Resume Next If o Is Nothing Then Set myUnion = rng ElseIf rng Is Nothing Then Set myUnion = o Else Set myUnion = Union(o, rng) End If On Error GoTo 0 End Function -Jim (see Organization field to figure out email address) . |
#44
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
This looks interesting but I cannot get it to work
correctly, a dot or two out of place perhaps. No. If you can't get it to work let me help. What was the error message and line? -Jim |
#45
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
This looks interesting but I cannot get it to work correctly, a dot or two out of place perhaps. No. If you can't get it to work let me help. What was the error message and line? -Jim Jim, Not directly a code halting with an error but more a case of as I also mentioned last post: As it stands it does not appear to return the non- intersecting areas of the ranges thrown at it. With the examples I posted: Sub test2() Dim RngA As Range, RngB As Range Set RngA = Range("b2:c3,f3:g4") Set RngB = Range("b2:g4") 'square or outer RngA Set RngA = NotIntersect(RngA, RngB) RngA.Select Debug.Print RngA.Address End Sub Here, RngA.Select errors because, in your func: Set y = Intersect(y, rng) is a non intersecting range, hence the function returns a non existant range. Or, Set RngB = Range("B2:C3,F3:G4") Set RngA = Range("B3:B5,C3:G3") Set RngA = NotIntersect(RngA, RngB) RngA.address returns: B4:B5,D3:G3 Instead of: F4:G4,B4:B5,B2:C2,D3:E3 ($'s trimmed) Have I missed something? Regards, Peter |
#46
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Have I missed something?
Yes. Function NotIntersect(rng As Range, x As Range) As Range rng is the source x is the range to be removed from the source I presented the function in such a way that x should be a single-area range, because calling the function within a loop... for each x in bigx.Areas NotIntersect(Selection, x).Select ....or whatever, would be a trivial exercise for the reader, and would detract from understanding the basic and very simple concept of what my code achieves, extremely efficiently. -Jim |
#47
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Hi Jim,
I presented the function in such a way that x should be a single-area range, because calling the function within a loop... for each x in bigx.Areas NotIntersect(Selection, x).Select ...or whatever, would be a trivial exercise In his initial post in this thread, KeepITcool explicitly excluded the loop approach: It MUST be fast.. thus a simple loop will never suffice. unions above 400 areas get dreadfully slow.. This need is reinforced by the repeated discussion in the thread of the 8192 non-contiguous areas bug in conjunction with comments such as the following (from KeepITCool): Done some basic testing but even at a:z60000 with 40% random non blanks.. returned 48 multiarea ranges(avg 7500 areas/range)in the collection. 90secs.. (1200k cells..372k areas.. but NO errors ! I suspect that testing your function in similar fashion would highlight the fundamental problem. My plagiaristic approach and those of Peter and KeepITcool were all predicated on a fast non-looping solution. --- Regards, Norman |
#48
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Jim,
Thank you for clarifying, I had been hoisted by a series of self induced presumptions. Although you introduced this as "For your amusement..." it is, as you say, extremely efficient. However I think not efficient to deploy with a large loop of areas the way you suggest. Eg to return the non- intersection of bigx and the single area that perfectly surrounds it - Invert a multiple range. With 100 areas the increased time compared with other methods is of no consequence, with the advantage of requiring neither DV nor CF. But with say 4,000 areas, in my testing, about a hundred times longer than the few seconds required using DV subtraction, or similar with KeepITcool's CF method. I don't think I've misunderstood anything this time, but if I have please advise. Once again thank you for posting the code, Peter PS written before seeing Norman's adjacent post I presented the function in such a way that x should be a single-area range, because calling the function within a loop... for each x in bigx.Areas NotIntersect(Selection, x).Select ....or whatever, would be a trivial exercise for the reader, and would detract from understanding the basic and very simple concept of what my code achieves, extremely efficiently. -Jim . |
#49
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Hi, Jim:
I was intrigued by your code. I tried it with the following Sub Test() Dim R As Range Set R = NotIntersect(Range("a2:d5"), Range("b3:c4")) If R Is Nothing Then MsgBox ("Nothing!") Else MsgBox R.Address End If Set R = NotIntersect(Range("b3:c4"), Range("a2:d5")) If R Is Nothing Then MsgBox ("Nothing!") Else MsgBox R.Address End If End Sub The first call works fine -- it would select all cells in A2:D5 except B3:C4. The 2nd line SHOULD produce the same result, right? But it doesn't. It returns Nothing. Also, if I call it with ranges A2:D5 and B3:D6, in that order, it does not include cells B6:D6, which are part of the 2nd range but not of the 1st. The routine would seem to require that the one range is entirely included in the other, and the larger range must be specified first. Was that your intent? On Wed, 04 Aug 2004 12:11:37 -0700, "jim.wilcox" wrote: get the the inverse of intersect For your amusement... Sub Test() NotIntersect(Selection, Application.InputBox("", , , , , , , 8)).Select End Sub Function NotIntersect(rng As Range, x As Range) As Range ' copyright 2001-2004 Jim Wilcox Dim y As Range On Error Resume Next If rng.Parent Is x.Parent Then With x Set y = myUnion(y, Range(Rows(1), .Rows(0))) Set y = myUnion(y, Range(Rows(Rows.Count), .Rows(.Rows.Count + 1))) Set y = Intersect(y, .EntireColumn) Set y = myUnion(y, Range(Columns(1), .Columns(0))) Set y = myUnion(y, _ Range(Columns(Columns.Count), .Columns(.Columns.Count + 1))) Set y = Intersect(y, rng) End With Set NotIntersect = y End If On Error GoTo 0 End Function Private Function myUnion(o As Range, rng As Range) As Range On Error Resume Next If o Is Nothing Then Set myUnion = rng ElseIf rng Is Nothing Then Set myUnion = o Else Set myUnion = Union(o, rng) End If On Error GoTo 0 End Function -Jim (see Organization field to figure out email address) |
#50
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Thanks, Peter and Norman.
KeepITcool explicitly excluded the loop approach: It MUST be fast.. thus a simple loop will never suffice. unions above 400 areas get dreadfully slow.. NotIntersect solves 400 areas in less than a second. repeated discussion in the thread of the 8192 non-contiguous areas bug Do we have a solution that solves 8000? NotIntersect does, but I do see that it takes too long -- many minutes. Forgive me, but do we need one? I don't read this newsgroup. I saw the original post in this thread during a search for something else, so I haven't read this bug discussion. FWIW, is it a bug? There are many things that spreadsheets aren't intended to do, and while it's fun to challenge the boundaries, perhaps it's also helpful to provide pointers to the more appropriate application(s). -Jim |
#51
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Was that your intent?
Yes. -Jim (see Organization field to figure out email address) P.S. I come from the old school of usenet. I mean, for questions like these, it seems to me that email is more appropriate, no? |
#52
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Jim,
Do we have a solution that solves 8000? KeepITcool does for his particular purpose, see SegmentedCells in one of his posts. I havn't figured how to adapt this for more generic use. I think Norman was working on something, privately - ? and a <g NotIntersect does, but I do see that it takes too long -- many minutes. Forgive me, but do we need one? Absolutely. For me not often 8000+ but certainly something fast for x000. FWIW, is it a bug? [re 8192 limit] In Excel this may be a sensible design limitation. F5 select 8000+ areas severely strains resources, assuming no other constraints which there probably are. But in VBA no error is returned if the limit is exceeded, incorrectly returns just the first cell. I'd call that a bug. This contrasts with: a) in vba, no cells found - generates trappable error b) Excel only, 8192+ generates Alert "selection too large" Something simple like count constants and one cell is returned, how to know if that's correct. Norman demonstrated the limit could even be 8191 ! and additionally a solution to trap the problem. Back to your function, my results in an old system (reducing outer-bigx with bigx by areas): Areas NotIntersect DV or CF method 400 1 sec 0.1 s 1000 15 s 0.5 s 4000 800 s 6 s Self evident. But I would not bet against the possibility your function could be radically adapted to reduce the time by a factor of 2 to 10, depending on no. of areas. Regards, Peter |
#53
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
P.S. I come from the old school of usenet. I mean, for questions like
these, it seems to me that email is more appropriate, no? I don't agree. I thought the purpose of these discussions was to help other users. Seeing only part of the discussion doesn't help them... |
#54
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
PS: My confusion is coming from the the name of your routine and the fact that
I haven't followed this thread. Given the name, NonIntersect, I expected the routine to take two ranges, create their union, then remove their intersection. I see from your comments in another message that you want to take the first range and remove from it any cells that are also part of the 2nd range. That's not the problem I expected, but presumably it's what the OP wanted. On Fri, 06 Aug 2004 16:14:01 -0700, "jim.wilcox" wrote: Was that your intent? Yes. -Jim (see Organization field to figure out email address) P.S. I come from the old school of usenet. I mean, for questions like these, it seems to me that email is more appropriate, no? |
#55
Posted to microsoft.public.excel.programming
|
|||
|
|||
algorithm to INVERT a multiarea selection ?
Re email:
If I display the header fields, the organization field gives your company name, not your email address at the company. I use Agent as my newsreader. Perhaps Outlook Express is different. these, it seems to me that email is more appropriate, no? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you invert your selection of cells in excel? | Excel Discussion (Misc queries) | |||
How can i invert the selection of cells in Microsoft Excel 2007 | Excel Worksheet Functions | |||
How do I invert a selection in Excel 2007? | Excel Discussion (Misc queries) | |||
Invert Excel Selection | Excel Discussion (Misc queries) | |||
help with algorithm | Excel Programming |