How to find all non blank cells in an array of cel

Question

I have some data in $A$1:$D$50. How can I put all non blank cells into a single column (say E) using a formula (regular or array one). All other examples I've found were about finding blank cells in a single column or a row.

Thanks

Answer 1

First enter the following UDF:

Public Function NonBlanks(rng As Range) As Variant
    Dim c As Collection, r As Range, CC As Long, i As Long
    Set c = New Collection
    For Each r In rng
        If r.Value <> "" Then
        c.Add (r.Value)
        End If
    Next r
    CC = c.Count
    ReDim Arout(1 To CC, 1 To 1) As Variant
    For i = 1 To CC
        Arout(i, 1) = c(i)
    Next i
    NonBlanks = Arout
End Function

Then select E1 thru E200 and enter the following array formula

=NonBlanks(A1:D50)

EDIT#1:

Without VBA, pick a "helper" column, say column F. In F1 enter:

=IF(OFFSET($A$1,MOD(ROW()-1,50),INT((ROW()-1)/50))="","",OFFSET($A$1,MOD(ROW()-1,50),INT((ROW()-1)/50)))

and copy down thru F200

Then apply your standard formula to column F

EDIT#2:

REFERENCE:

By standard formula I mean something like Pearson's method