sbNoBlank

Unfortunately Excel © lacks a function which returns all non-blank input cells. I have developed two functions which resolve this: sbNoBlank can be used in worksheets, sbNoBlankVBA via VBA.

Please read my Disclaimer.

Function sbNoBlank(ParamArray v() As Variant) As Variant
'sbNoBlank returns all non-empty cells of all input areas
'in one result array. Keep in mind to array enter this
'function if you call it from a worksheet.
'Reverse("moc.LiborPlus.www") V0.11 PB 05-Oct-2010
Dim vI As Variant, vJ As Variant
Dim i As Long, lvdim As Long

With Application.Caller
If .Rows.Count > 1 And .Columns.Count > 1 Then
    'Only one-dimensional output range allowed
    sbNoBlank = CVErr(xlErrRef)
    Exit Function
End If
lvdim = .Rows.Count * .Columns.Count
ReDim vR(1 To lvdim) As Variant 'Result array
i = 0
For Each vI In v
    For Each vJ In vI
        If Len(vJ) > 0 Then
            i = i + 1
            If i > lvdim Then
                If .Rows.Count > 1 Then
                    sbNoBlank = Application.WorksheetFunction.Transpose(vR)
                Else
                    sbNoBlank = vR
                End If
                Exit Function
            End If 'Delete this line and see most amazing compile errors
            vR(i) = vJ
        End If
    Next vJ
Next vI
For i = i + 1 To lvdim
    vR(i) = vbNullString
Next i
If .Rows.Count > 1 Then
    sbNoBlank = Application.WorksheetFunction.Transpose(vR)
Else
    sbNoBlank = vR
End If
End With
End Function

Function sbNoBlankVBA(ParamArray v() As Variant) As Variant
'sbNoBlankVBA returns all non-empty cells of all input areas
'in one result array.
'Reverse("moc.LiborPlus.www") V0.10 PB 05-Oct-2010
Dim vJ As Variant
Dim i As Long, lvdim As Long

lvdim = 100 'Let us start with a small dim for result array
ReDim vR(1 To lvdim) As Variant 'Result array
i = 0
For Each vJ In v
    If Len(vJ) > 0 Then
        i = i + 1
        If i > lvdim Then
            lvdim = 10 * lvdim
            ReDim Preserve vR(1 To lvdim)
        End If
        vR(i) = vJ
    End If
Next vJ
ReDim Preserve vR(1 To i) As Variant
sbNoBlankVBA = vR
End Function