Problem collecting Range Values

[richcoleuk]

I am trying to collect and Range, that is made up of a number o
"sub-ranges" if you like.

I create three ranges the normal way by giving cell references. Thi
works fine and my three ranges hold the values that I need in th
Value2 attribute.

The ranges aren't next to each other and I want to join them into on
range, so I've used the Union() method. This combines the ranges an
if I now use the select method this highlights my range and you can se
that it is the range I'm after.

However the value2 attribute of this combined range, doesn't hold al
the values covered by the range? In fact it only contains values fro
the first range I pass into Union()

Any ideas? Or other solutions to collecting non-contigous ranges

 

[Martin]

You're right - the locals window only shows Value2 for the first range - weird!

However, if you use Value you can pick up all the cells in the range, eg:

Dim myRange As Range
Dim myCell As Range
Set myRange = Application.Union(Range("A1:A3"), Range("A5:A6"),
Range("A8:A10"))
For Each myCell In myRange
MsgBox myCell.Value
Next

 

[richcoleuk]

Okay thanks for that,

How ever I am using some C# behind excel and I need to be able to pas
in a range that has a value2 attribute that holds all the data from th
multiple ranges.

How can I make sure this 1 range has all the data stored in Value2

 

[paul.robinson]

Hi
You could output an array of variants.
Each range object has an Areas property to count the disconnected bits
of the range. for your three areas you could try

Function Tester() as Variant
Dim OutputValues() as Variant
AreaCount = Selection.Areas.Count
ReDim OutputValues(1 to AreaCount)
for i = 1 to AreaCount
OutputValues(i) =Selection.Areas(i).Value
next i
Tester = OutputValues
end function

Then OutPutValues(2) would be the array of values in area 2 of the
selection.

regards
Paul

 

[richcoleuk]

Okay I think I'm nearly there. The only trouble I have now is that th
C# method I need to use expects a Range[] and i don't think it like
being passed a Variant, in the example you gave Teste

 

[richcoleuk]

No one have anyideas how i can convert to a Range[]

 

[paul.robinson]

Hi
I sent you solution privately, but just for this thread:

An array of Ranges output:

Function Tester2() As Variant
Dim OutputRanges() As Range, i As Integer
AreaCount = Selection.Areas.Count
ReDim OutputRanges(1 To AreaCount)
For i = 1 To AreaCount
Set OutputRanges(i) = Selection.Areas(i)
Next i
Tester2 = OutputRanges
End Function

Public Sub test()
Dim myValues As Variant
For i = 1 To UBound(Tester2)
Set Testarea = Tester2(i)
MsgBox Testarea.Address
Next i
End Sub

A Collection of Ranges output:

Function Tester2() As Collection
Dim OutputRanges As New Collection, i As Integer
AreaCount = Selection.Areas.Count
For i = 1 To AreaCount
OutputRanges.Add Selection.Areas(i), "Area" & i
Next i
Set Tester2 = OutputRanges
End Function

Public Sub test()
Dim Testarea as Range
Dim myValues As Variant
For i = 1 To Tester2.Count
Set Testarea = Tester2("Area" & i)
MsgBox Testarea.Address
Next i
End Sub

regards
Paul