Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using a fragmented named range (Excel 2000 et al.)

    I came a cropper with this yesterday, retired home to lick my wounds.
    The function rngClean finds a clean column of data. We gave it a range that was a set of areas (as in, using the function picker(fx) and using Ctrl-Click to select several non-contiguous sets of cells).
    My rngClean function as written couldn't cope - it processed only the first range in the list.
    So I wrote a crude Unionizer to bring the cells into a single range.
    It seems to do the job.<pre>Public Function rngUnion(rngIn As Range) As Range
    ''' If more than one area in the given range, return the union of the areas.
    Dim rngResult As Range
    Set rngResult = rngIn
    If rngIn.Areas.Count > 1 Then
    Dim lngAreas As Long
    lngAreas = rngIn.Areas.Count
    For lngAreas = 2 To rngIn.Areas.Count
    Set rngResult = Union(rngResult, rngIn.Areas(lngAreas))
    Next lngAreas
    Else
    End If
    Set rngUnion = rngResult
    End Function
    </pre>


  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Using a fragmented named range (Excel 2000 et

    Alternatively, loop the Areas:

    Dim rng As Range
    For Each rng In Application.Selection.Areas
    rng.Interior.ColorIndex = 4
    Next rng

    Actually, this isn't a good example as most formatting properties can be applied to a noncontigous range in one fell swoop:

    Application.Selection.Interior.ColorIndex = 4
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using a fragmented named range (Excel 2000 et

    > Alternatively, loop the Areas:

    (24 hours later) You are right.
    I have since discovered that although the unionized range shows up in Local Variables with all 6 rows by 5 columns = 30 cells under its wing, it can't be used to perform any useful task.

    For example, if I want to examine all the cells in the leftmost column ("B5, B6, B8, B9, B11 and B12" in my example), I must loop through all areas (3 in my example) and inspect the leftmost column in each. There appears to be little value in forming a union if I must still loop through all areas.

    I am avoiding a discussion of the impact of 3 areas, the first being four columns wide, the second six columns wide, and the third two columns wide.

Posting Permissions

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