I found some code on these pages which does just want i need.

However, i am trying to put the results into an array for a list box, so that column will contain the text "Box one" to "Box Eight". And column two will contain the data which has been extracted with:

Set rng = ActiveSheet.Range("c6:j6")
For Each cell In rng
If Not IsEmpty(cell) Then
ListBox1.AddItem cell.Value
End If
Next

so far i have played around with:

Dim i, j, Rows As Double
j = 1
For i = 1 To MyArray
myarray(i,j)= cell.Value
next i
trying different combinations