Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Multiple Ranges (Excel97 SR2)

    I am trying to select multiple non-contiguous ranges on a worksheet to do some formatting on the cells in the ranges (setting borders, fonts, etc).

    Can anybody tell me why this code works :
    With objXLSheet
    strRangeAddr = ""
    Set objXLRange = .Range(.Cells(18, 3), .Cells(18, 22))
    strRangeAddr = objXLRange.Address
    Set objXLRange2 = .Range(.Cells(24, 3), .Cells(24, 22))
    strRangeAddr = strRangeAddr & ", " & objXLRange2.Address
    Set objXLRange3 = .Range(.Cells(27, 3), .Cells(28, 22))
    strRangeAddr = strRangeAddr & ", " & objXLRange3.Address
    Set objXLRange4 = .Range(.Cells(31, 3), .Cells(31, 22))
    strRangeAddr = strRangeAddr & ", " & objXLRange4.Address
    Set objXLMultiRange = .Range(strRangeAddr)
    End With

    ...and this code returns an Automation error n the last line when using the Union method

    With objXLSheet
    Set objXLRange = .Range(.Cells(18, 3), .Cells(18, 22))
    Set objXLRange2 = .Range(.Cells(24, 3), .Cells(24, 22))
    Set objXLRange3 = .Range(.Cells(27, 3), .Cells(28, 22))
    Set objXLRange4 = .Range(.Cells(31, 3), .Cells(31, 22))
    Set objXLMultiRange = Union(objXLRange, objXLRange2, objXLRange3, objXLRange4)
    End With

    Any help gratefully received

    Nick

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Can you have a union of non-contiguous ranges?

    I thought the union of two ranges was where they overlapped and that the definition of non-contiguous ranges was that they didn't overlap or touch, hence the code failure.

    Brooke

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Yes, you can.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Jan, would you mind expanding on that and explaining where my logic falls over? It just seems wrong to me.

    Brooke

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Nick,

    Maybe the problem lies outside the snippet of code you posted, as it seems to work for me.

    I attach a workbook using the code as posted for you to look at. Just click the button to test.

    Andrew
    Attached Files Attached Files

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    I meant to point out also that you could give a non-contiguous range a RangeName, and then just use that name to select the range, as in Range("RangeName").Select.

    Andrew

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    works fine for me, too.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Hi Brooke,
    I believe that the area where two ranges overlap is the Intersection whereas the Union is the range that includes all cells in the 2 original ranges.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    The Intersection() gives where they overlap, the Union() should give all of the cells in all of the ranges, and both can be non-contiguous. For example, the intersection of "A1:A2", "C1:C2", "A2:C2" is "A2" and "C2", and the Union is "A1", "C1", "A2:C2".
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Thanks to you and Legare for pointing out that I need to go stick my head in a textbook!

    Brooke

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Brooke,

    Below is a copy from xl2K OLH:

    Thus Union is the way to create ranges consisting of multiple areas (Non-adjacent ranges).

    Clear as mud?

    ==============
    Referring to Multiple Ranges


    Using the appropriate method, you can easily refer to multiple ranges. Use the Range and Union methods to refer to any group of ranges; use the Areas property to refer to the group of ranges selected on a worksheet.

    Using the Range Property

    You can refer to multiple ranges with the Range property by putting commas between two or more references. The following example clears the contents of three ranges on Sheet1.

    Sub ClearRanges()
    Worksheets("Sheet1").Range("C59,G9:H16,B1418"). _
    ClearContents
    End Sub
    Named ranges make using the Range property to work with multiple ranges easier. The following example works when all three named ranges are on the same sheet.

    Sub ClearNamed()
    Range("MyRange, YourRange, HisRange").ClearContents
    End Sub
    Using the Union Method

    You can combine multiple ranges into one Range object using the Union method. The following example creates a Range object called myMultipleRange, defines it as the ranges A1:B2 and C34, and then formats the combined ranges as bold.

    Sub MultipleRange()
    Dim r1, r2, myMultipleRange As Range
    Set r1 = Sheets("Sheet1").Range("A1:B2")
    Set r2 = Sheets("Sheet1").Range("C34")
    Set myMultipleRange = Union(r1, r2)
    myMultipleRange.Font.Bold = True
    End Sub
    Using the Areas Property

    You can use the Areas property to refer to the selected range or to the collection of ranges in a multiple-area selection. The following procedure counts the areas in the selection. If there is more than one area, a warning message is displayed.

    Sub FindMultiple()
    If Selection.Areas.Count > 1 Then
    MsgBox "Cannot do this to a multiple selection."
    End If
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Jan,

    Thankyou - now I just have to remember all that!

    Brooke

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Multiple Ranges (Excel97 SR2)

    Nah, don't memorise it, just type press F1 from the VBE and search for "Using multiple ranges"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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