Hi Ivan,
Heres a function that I have just whipped up which should solve your problem.
Obviously if you need to cusomise it, then feel free to do so.
It will return an Array from a Range where any cells are not blank.
Code:
Public Function NonBlankCellValuesFromRange() As String()
On Error GoTo NonBlankCellValuesFromRange_Err
'Create Reference to the Working Sheet, for this example
'use the Active Sheet.
Dim ws As Worksheet
Set ws = ActiveSheet
'First Define the Range to Work with
'Use the Range posted (A10:Z30)
Dim r As Range
Set r = ws.Range("A10:Z30")
'Create an Array to hold our values.
Dim arr() As String
Dim arrSize As Long
arrSize = 0
'Need to loop through each column within each row,
'adding to an Array in NOT empty.
Dim row As Range, col As Range
For Each row In r.Rows
For Each col In row.Columns
'If the Value is not Blank, than add to the Array.
If Not (col.Value = vbNullString) Then
ReDim Preserve arr(arrSize)
arr(arrSize) = col.Value
arrSize = arrSize + 1
End If
Next
Next
'Return the Array
NonBlankCellValuesFromRange = arr
'Clean Up
Set r = Nothing
NonBlankCellValuesFromRange_Exit:
Exit Function
NonBlankCellValuesFromRange_Err:
Debug.Print "Error!" & vbCrLf & "Number: " & Err.Number & vbCrLf & Err.Description
End Function
I hope this helps.
Regards,
Rob
<center>
"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>
Array problem
