Sulprobil
Search…
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
Last modified 2yr ago
Copy link