CODEWITHSUNDEEP
excelvba
Gary's Student
Gary's Student

Reputation: 96753

Constructing a `Range` containing a cell multiple times

I can easily construct a Range in which the same cell appears twice (in this case cell B1):

Sub IAmTheCount()
    Dim r1 As Range, r2 As Range, r3 As Range
    Set r1 = Range("A1:B1")
    Set r2 = Range("B1:B2")
    Set r3 = Union(r1, r2)
    MsgBox r3.Count
End Sub

How can I construct a range in which the same cell appears more than twice??

Upvotes: 0

Views: 78

Answers (1)

barryleajo
barryleajo

Reputation: 1952

I'm not sure if I am inside your head or not with this, but this seems to count and include cell B1 three times and act as an explicit range.

Sub IAmTheCount()
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range
    Set r1 = Range("A1:B1")
    Set r2 = Range("B1:B5")
    Set r3 = Range("B1:C2")
    Set r4 = Union(r1, r2, r3)
    MsgBox r4.Count & " - " & r4.Address
    r4.Interior.ColorIndex = 4
End Sub

Upvotes: 3

Related Questions