Assigning a Range to a dynamic array

[Tommy]

Hey guys,

I have a function that I would like to use to return an array of
ranges. It is important that the returned array however is dynamic as
I would then like to be able to use a loop to union the ranges
regardless of how many there are. Here is the code so far:

Public Function SelectSeries(SeriesColumnLetter) As Variant()

Sheets("Data").Select

Dim SeriesRange(1 To 4)
Dim DataSelection(1 To 4)
Dim DataSource() As Variant

Dim i

For i = 1 To 4

If SeriesColumnLetter(i) <> " " Then

Set SeriesRange(i) = Worksheets("Data").Range(SeriesColumnLetter(i) &
"7:" & SeriesColumnLetter(i) & "65536")
DataNumber = Application.WorksheetFunction.CountA(SeriesRange(i))
DataSelection(i) = SeriesColumnLetter(i) & "7:" &
SeriesColumnLetter(i) & (DataNumber + 6)

Set DataSource(i) = Worksheets("Data").Range(DataSelection(i))

Else

MsgBox ("No column letter!")

End If

Next

SelectSeries = DataSource()

End Function

Four SeriesColumnLetter variables are input into the function.Usually
these values are a letter, but sometimes they are blank. If they are
blank, I would like them to be excluded from the returning array i.e.
if 1 out of the 4 is blank, then the returning array would contain 3
values.

At the line:

Set DataSource(i) = Worksheets("Data").Range(DataSelection(i))

I get the error: 'Subscript out of range'. I guess this is because the
array has been declared dynamically. How can I get around this and
return a dynamic array??

Thanks and regards,

Tom

 

[Rick Rothstein \(MVP - VB\)]

I've tried reading your code over a few times, but I am having trouble
following what you are trying to do. Forget about the code (for now), can
you explain in words what your data looks like, what you are passing into
the function (you seem to be processing it as an array, is it?) and what
"ranges" you want the function to pass back?

Rick