Range Areas

When you need to work with ranges that are not connected (contiguous), use the Areas collection.

A Range contains an Areas collection containing one or more Ranges.
Most commonly it’s a Selection containing Areas.

Selected Areas

Hold down the Control key and select cells A1:A5 and C1:C5
The highlighted cells are represented by the Selection object.
The Selection object has an Areas collection.
Item 1 of Areas is the range A1:A5
Item 2 of Areas is the range C1:C5

Because items of Areas are just Ranges, you can do operations like:

Selection.Areas(2).Font.Bold = True

One of the common uses of Areas is the Worksheet SelectionChange event.
The user can create any sort of Selection so handling Areas is a good idea.

This example writes to the status bar as you select cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim str As String, rng As Range
    For Each rng In Target.Areas
        str = str & IIf(str = “”, “”, “, “) & rng.Address(False, False)
    Application.StatusBar = “Selected Areas: “ & str
End Sub

To create a range with Areas, use Union.
When you Union two ranges together, a range is returned which contains an Areas collection with two range items.

Sub test()
    Dim rng1 As Range, rng2 As Range, rngSuper As Range
    Set rng1 = Range(“A1:A5”)
    Set rng2 = Range(“C1:C5”)
    Set rngSuper = Union(rng1, rng2)
    MsgBox rngSuper.Areas.Count
End Sub

Exception: If the Union ranges create a contiguous range it gets treated as a single Area.
Try the code but instead of C1:C5 use B1:B5

Posted in Uncategorized

7 thoughts on “Range Areas

  1. some problem occurs if the intervening column is a blank column. I think in that case it treats as one area. Or am I wrong?

  2. Note that a connected range is not necessarily contiguous:


    The first makes A1:B5, which is a nice rectangular contiguous range. The second is obviously discontiguous. The third is not considered contiguous by Excel, because it’s missing a corner. You need to refrence both areas to describe its shape.

    Another strangeness is that the areas are numbered in the order they are selected or defined. Both of these define the same bunch of cells on the worksheet, but in one case, column A is specified first, in the other column C is specified first. Area(1) and Area(2) switch, as does the address of the combined range.





    You have to be careful, because this line of code from the discussion above will have different effects:

    MyRange.Areas(2).Font.Bold = True

    – Jon

  3. Hi,

    I want to find the Precedents of a given cell. I am able to find the precedents in the Active sheet.

    Is it possible to find the Precedents of a given cell in an Inactive Worksheet?

    Plz reply.



  4. If I have two distinct discontinuos lists which individually are three columns wide but the rows may vary from 0:N.

    How would one select the contents of these two ‘AREAS’ programatically if there were only one item in either area? (0 items and many items is simple.)

    Range(Selection, Selection.End(xlRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

    won’t work with one row because you get to ‘davy jones locker’ pretty fast.


    won’t work because there are headers and one column is populated with formulas


  5. Here is what I did:

    Sub doFillOrders()
    Dim oTotalOrder As Range
    Dim oStandingOrder As Range
    Dim oSupplementalOrder As Range
    Dim lStordItems As Long
    Dim lSupItems As Long

    Application.ScreenUpdating = False

    lStordItems = Application.WorksheetFunction.CountA(Range(“stordItems”)) ‘ standing order
    lSupItems = Application.WorksheetFunction.CountA(Range(“supItems”)) ‘ supplemental order

    Range(Selection, ActiveCell.Offset(lStordItems – 1, 2)).Select
    Set oStandingOrder = Selection

    Range(Selection, ActiveCell.Offset(lSupItems – 1, 2)).Select
    Set oSupplementalOrder = Selection

    Application.ScreenUpdating = True
    Set oTotalOrder = Union(oStandingOrder, oSupplementalOrder)

    ‘ no null fields please
    If isCompleteOrder(oTotalOrder) Then
    Call doUpdateSales(oTotalOrder) ‘ update access db
    MsgBox “Incomplete Data, Check Entries and Try Again”, vbCritical, “ERROR”
    End If

    Set oStandingOrder = Nothing
    Set oSupplementalOrder = Nothing
    Set oTotalOrder = Nothing

    End Sub

  6. an example, what happen if i have the table with the id and name the programs, and I have another table that i want give the id and return the name program using DGET.. is not posible because DGET need header for criteria and only i have 1 header for this field.. if I can write something like “id”:A2 instead of A1:A2 is good enough. because i using “id”:A3 , “id”:A4 and so on..

    note: obviously “id”:A2 is not working is only an possible solution, can be also {“id”,A2} or “id”&A2.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.