You can loop thru cells and test for a condition, for example, the following
untested code only adds data to the array if the cell is not blank:

Dim rCell as Range
Dim lX as long
Dim vArray() as Variant
Dim sMSG

'Load the array:
For Each rCell in ActiveSheet.UsedRange.Columns(1).Rows
If Trim(rCell.Formular1c1) < "" Then '<<< filter out blank cells
lX = lX + 1
ReDim Preserve vArray(lX)
vArray(lX) = rcell.Value
End If
Next rCell

'now check the contents:
For lX = 1 to Ubound(vArray)
sMsg = sMsg & vArray(lX) & vbCr
Next lX
Msgbox sMsg

HTH


"PMC1" wrote:

Hi,

Using Excel 2003 VBA I'm looking to Populate a 1 dimensional Array
with a Discontiguous values.

For example, instead of fully declaring a range like this
Array(1,2,3,7,8,9) I want ot pupulate the array by declaring the range
and then use something like a loop to populate the array e.g.

Dim myArRanges(1 to 3, 7 to 9)
Redim MyArray(myArRanges)

Could anyone suggest how this might be done.

Thanks

...pc