Is it possible to populate an array from a column of data which has gaps in
it and have the array filled only with data?

There are probably more elegant ways of doing it, but you could populate an
array like this (change the first set statement to whatever range you need -
I am using ColumnA on Sheet1 (note this is the codename) for my example)

Sub Test()
Dim Data() As Variant
Dim i As Long
Dim Count As Long
Dim Isect As Range

Set Isect = Application.Intersect(Sheet1.UsedRange, _
Sheet1.Columns(1))

ReDim Data(1 To Isect.Cells.Count)

For i = 1 To Isect.Cells.Count
If Not IsEmpty(Isect.Cells(i)) Then
Count = Count + 1
Data(Count) = Isect.Cells(i).Value
End If
Next i

ReDim Preserve Data(1 To Count)

For i = LBound(Data) To UBound(Data)
MsgBox Data(i)
Next i

End Sub


Or, if you wanted to work with the range directly and not use an array you
could use this (or something similar):

Sub Test2()
Dim Isect As Range
Dim VisibleCells As Range
Dim x As Range

Set Isect = Application.Intersect(Sheet1.UsedRange, _
Sheet1.Columns(1))

Isect.SpecialCells(xlCellTypeBlanks).EntireRow.Hid den = True
Set VisibleCells = Isect.SpecialCells(xlCellTypeVisible)
Isect.EntireRow.Hidden = False

For Each x In VisibleCells
MsgBox x.Value
Next x

End Sub

"Dale" wrote:

Is it possible to populate an array from a column of data which has gaps in
it and have the array filled only with data?