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.
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:
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.
Dim str As String, rng As Range
For Each rng In Target.Areas
str = str & IIf(str = “”, “”, “, “) & rng.Address(False, False)
Next
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.
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
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?
Note that a connected range is not necessarily contiguous:
?union(range(“A1:A5?),range(“B1:B5?)).Areas.Count
1
?union(range(“A1:A5?),range(“C1:C5?)).Areas.Count
2
?union(range(“A1:A5?),range(“B2:B5?)).Areas.Count
2
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.
?union(range(“A1:A5?),range(“C1:C5?)).Address(false,false)
A1:A5,C1:C5
?union(range(“A1:A5?),range(“C1:C5?)).Areas(1).Address(false,false)
A1:A5
?union(range(“C1:C5?),range(“A1:A5?)).Address(false,false)
C1:C5,A1:A5
?union(range(“C1:C5?),range(“A1:A5?)).Areas(1).Address(false,false)
C1:C5
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
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.
Thanks,
Cheers,
anantha
Anantha,
I think this is quite difficult. I’ve never dont it myself but understand it involves creating the formula auditing arrows and navigating them to the next sheet.
This Groups search might assist:
Google Groups Excel Precedents
Cheers,
Rob
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.
Range(“firstCellInArea”).CurrentRegion.Select
won’t work because there are headers and one column is populated with formulas
doco
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(“firstItemQty”).Select
Range(Selection, ActiveCell.Offset(lStordItems – 1, 2)).Select
Set oStandingOrder = Selection
Range(“supplement_begin”).Select
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
Else
MsgBox “Incomplete Data, Check Entries and Try Again”, vbCritical, “ERROR”
End If
Set oStandingOrder = Nothing
Set oSupplementalOrder = Nothing
Set oTotalOrder = Nothing
End Sub
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.