Solved: Creating sub array from a range

[snoopfan]

Dear all,

I am trying to implement a function which will help me extract a subarray from a range. E.g: if I have a range specified on a different sheet, let's say around 30 numbers and they are in an ascending order. Then in another sheet I would like to put two values which will be min and max for the new subarray extracted from those 30 numbers and save them in an array in VBA.

e.g. original range: 3 , 4 , 4.4, 6, 7.2, 10, ....., 24, 26, 28, 32, 40

i specify min: 10 max: 28

output: 10, ...., 24, 26 ,28 saved in an array in vba

Anyone any idea?

[Bob Phillips]

Function SubArray(data As Range, nMin As Double, nMax As Double) As Variant
Dim tmp As Variant
Dim cell As Range
Dim i As Long

ReDim tmp(1 To data.Cells.Count)
For Each cell In data

If cell.Value >= nMin And cell.Value <= nMax Then

i = i + 1
tmp(i) = cell.Value
End If
Next cell

If i > 0 Then

ReDim Preserve tmp(1 To i)
SubArray = tmp
End If
End Function

and use like this

myArray = SubArray(Range("A1:A13"), 10, 28)

[snoopfan]

Thank you!