Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Invert the selection quest! (Excel 2003>)

    Hi,

    I am still on a quest to find code that will invert the selection. In my attempts to find something I came across the code pasted below, by Jim Wilcox (found here) The majority of the code is Function procedures with an input box which prompts for the range. When I use the input to select a range I was expecting the selection to invert. When I have selections in the range I select it debugs with object or variable not set?

    Any help would be nice
    TX

    Code:

    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
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invert the selection quest! (Excel 2003>)

    It's confusing if you ask more or less the same question in several threads! <img src=/S/cranky.gif border=0 alt=cranky width=18 height=25>

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invert the selection quest! (Excel 2003>)

    I will clarify what I am after.

    Say I have code that selects cells with conditional formatting (the code selects the yellow cells). I do not want to act on these cells, but rather on the the cells without conditional formatting. When I run a second macro, it should prompt with an input to select a range to invert. So now I draw a selection around cells A2:E4. (Remember that the first macro has already selected the cells represented in yellow.) Now the macro has the selected cells and the range in which it needs to work. Now it inverts the selection to be all the non yellow cells within the range A2:E4.

    Thank
    PS: I think this is what Jim's code should do, but it debugs with an object defined error?
    Attached Images Attached Images
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invert the selection quest! (Excel 2003>)

    I don't understand the code by Jim Wilcox, so I wrote something myself.
    <code>
    Sub InvertSelection()
    Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Please select a range", Type:=8)
    Set rng = Difference(rng, Selection)
    If rng Is Nothing Then
    MsgBox "Nothing to select!", vbInformation
    Else
    rng.Select
    End If
    End Sub

    Function Difference(rng1 As Range, rng2 As Range) As Range
    Dim rngCell As Range
    If rng1 Is Nothing Then
    'Set Difference = Nothing
    ElseIf rng2 Is Nothing Then
    Set Difference = rng1
    Else
    For Each rngCell In rng1
    If Intersect(rngCell, rng2) Is Nothing Then
    If Difference Is Nothing Then
    Set Difference = rngCell
    Else
    Set Difference = Union(Difference, rngCell)
    End If
    End If
    Next rngCell
    End If
    End Function
    </code>
    The function Difference returns the difference of two ranges, i.e. all cells in rng1 that do not belong to rng2.
    The macro InvertSelection uses this to "invert" the selection with respect to the range provided by the user when prompted.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invert the selection quest! (Excel 2003>)

    Hans,

    thankyou... <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> (x1000000000000000000000000)

    <img src=/S/kiss.gif border=0 alt=kiss width=34 height=15>
    This is perfect (more than perfect!)
    PS: Is there any copyright on this code, or can I use it freely?
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invert the selection quest! (Excel 2003>)

    You can use it freely. If you put it in something you use for work, or something you sell, it would be nice if you added a comment that the code was provided by Woody's Lounge.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invert the selection quest! (Excel 2003>)

    Hi Hans,

    You did provide a small explanation of the code below, but could I ask for a little more explanation. I have stepped through the code and even looked at the locals windw, but it does not tell me much. Please do not write an essay, but just inform me to the logic behind setting all the variables to nothing and then doing something with nothing <img src=/S/drop.gif border=0 alt=drop width=23 height=23>!!

    Also, why did you comment out:
    If rng1 Is Nothing Then
    'Set Difference = Nothing
    ElseIf rng2 Is Nothing Then...

    TX
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invert the selection quest! (Excel 2003>)

    If the first argument is an empty range, the Difference function should return an empty range. It is not necessary to set Difference to Nothing explicitly, because an object variable starts out as Nothing by default. I left the line Set Difference = Nothing as a comment to explain that Nothing is returned in this situation. If it bothers you, you can remove the line.

    If neither argument is an empty range, the code loops through the cells of the first range and if they are not in the second one, it adds them to the Difference range using the Union function. However, Union raises an error if either of its argument is an empty range. So the first time we encounter a suitable cell, we don't use Union, but set Difference to this cell. Next time, Difference is not empty any more and we can use Union.

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invert the selection quest! (Excel 2003>)

    Thank you.
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •