dear xld,
many thanks for your advice.
Now i know why i kept getting error messages for the whole day.
I adopt another method which is now working for me
For i = 2 To RowCount If Cells(i, 8).Value = "none" Then arraySize = arraySize + 1 End If Next i ReDim myArray(arraySize - 1) As Variant n = 0 For i = 2 To RowCount If Cells(i, 8).Value = "none" Then myArray(n) = Cells(i, 6).Value n = n + 1 End If Next i FilteredData = myArray