And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook and you can
stand a 20-millisecond or so hit in execution speed, the coding can be
somewhat simpler:

Sub(a)
Dim MyArray() As Integer
Assign Range("A1:AP1").SpecialCells(xlCellTypeConstants), MyArray
End Sub

Alan Beban

Alan Beban wrote:

It would speed things up to take the ReDim [Preserve] out of the loop:

Sub a()
Dim MyArray() As Integer, rng As Range
Dim iCell As Range, Counter As Integer
Set rng = Range("A1:AP1").SpecialCells(xlCellTypeConstants)
ReDim MyArray(1 To rng.Count)
Counter = 1
For Each iCell In rng
MyArray(Counter) = iCell.Value
Counter = Counter + 1
Next
End Sub

Alan Beban

Jim Rech wrote:

If I wanted to populate an array of integers from the range A1 to AP1,
some or all of which may have entries, I'd do it like this:

Sub a()
Dim MyArray() As Integer
Dim Cell As Range, Counter As Integer
For Each Cell In Range("A1:AP1").SpecialCells(xlCellTypeConstants)
Counter = Counter + 1
ReDim Preserve MyArray(1 To Counter)
MyArray(Counter) = Cell.Value
Next
End Sub