Option Explicit
Sub Test()

Dim myArr() As Variant
Dim iCtr As Long
Dim myRng As Range
Dim myCell As Range

Set myRng = Sheet1.Range("mynamedrange")
ReDim myArr(1 To myRng.Cells.Count)

iCtr = 0
For Each myCell In myRng.Cells
If myCell.Value = "" Then
'skip it
Else
iCtr = iCtr + 1
myArr(iCtr) = myCell.Value
End If
Next myCell

If iCtr = 0 Then
'nothing found!
Else
ReDim Preserve myArr(1 To iCtr)
Worksheets("Sheet2").Cells(5, 5).Value = Join(myArr, ", ") & "."
End If

End Sub



brzak wrote:

Can someone please point out what is wrong with the following:

Sub Test()
Dim asdf() As Integer, i As Integer
For i = 0 To 5
asdf(i) = i
Next i
End Sub

doesn't work even if Option Base 0 were to be included, though
obviously isn't relevant to my problem

Whereas it likes:

Sub Test_()
Dim asdf(0 To 5) As Integer, i As Integer
For i = 0 To 5
asdf(i) = i
Next i
End Sub

what i'd actually like to do is loop through a named range, and assign
to the array only cells that meet a certain criteria, and join those
values to output to another cell i.e.

Sub Test()

'Two worksheets on workbook, as in default
'Sheet1 contains has a named range, "MyNamedRange", 3 by 1 in size
'cell A1 has text "apple"
'cell A2 is blank
'cell A3 has text "banana"

Dim asdf() As String, i As Integer, arr_size As Integer

arr_size = 0

For i = 0 To 2 'the named is range is 5x1, say a1:a5
'If
Len(Workbooks("Book1").Sheet1.Range("mynamedrange" ).Cells(i, 1).Value)
1 Then
If Len(Sheet1.Range("mynamedrange").Cells(i + 1).Value) 0
Then
asdf(arr_size) = Sheet1.Range("MyNamedRange").Cells(i +
1).Value
arr_size = arr_size + 1
End If
Next i

' would like array to be ("apple", "banana", "") at this point
' and at this point arr_size = 2

ReDim Preserve asdf(0 To arr_size - 1) 'resize

Worksheets("Sheet2").Cells(5, 5).Value = Join(asdf, ", ") & "."

End Sub

note this sub doesn't actually work, to run it, replace:
"Dim asdf() As String" with "Dim asdf(0 to 2) As String"
and comment the Redim Preserve line as array has been dimensioned.

This is really bugging me, and i know it's something stupid but just
can't see it!

Thanks in advance

--

Dave Peterson