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