This page has been replaced. Click here if you are not automatically redirected.

You can use a formula to return only the non-blank cells from a range. The following function will return all the cell values in a range named BlanksRange that are not empty.

Create a range name, with the same number of rows as BlanksRange called NoBlanksRange.  The range NoBlanksRange must have the same number of rows as BlanksRange but it need not be in the same row numbers.  Enter the following Array Formula in the first cell of NoBlanksRange, and then use Fill Down to fill out the range:

=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-
COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL(
(IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),
ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

The first N rows of NoBlanksRange will contain the N non-blank cells of BlanksRange.  Note that this is an array formula, so you must press Ctrl+Shift+Enter rather than just Enter when you first enter the formula and whenever you edit it later. If you do this properly, Excel will display the formula enclosed in curly braces { }.

Note that if you do not use named ranges and enter the actual cell references, you must use absolute cell references (e.g., $B$1) rather than relative cell references (e.g., B1).

Example

As you can see here, the values in BlanksRange  
are shifted up, as the blank entries are removed.

Download a workbook illustrating these formulas and VBA code.

You can also use VBA to create a NoBlanks Function. The code is shown below:

Function NoBlanks(DataRange As Range) As Variant()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NoBlanks
' This function returns an array that consists of the non-blank values
' in DataRange. The function must be array-entered into the complete range
' of worksheet cells that are to receive the result. For example, if
' you want the results in B1:B10, select that range, type
'       =NOBLANKS(A1:A10)
' in B1 and press CTRL+SHIFT+ENTER rather than just enter.
' This will cause the function to fill B1:B10 with the N non-blank
' entries in A1:A10, and place vbNullStrings in N+1 to 10.
' The input DataRange must have exactly 1 row or 1 column. You
' can't enter a two-dimensional array. The formula must be
' entered into a single column or singe row. You cannot
' enter the formula in a two dimensional array. If the formula
' is entered into a two-dimensional range, or if DataRange is a
' two dimensional range, the function will return #REF errors.
' The size of the array is the greater of the number of cells
' into which it was entered and the number of cells in the input
' DataRange.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim N As Long
Dim N2 As Long
Dim Rng As Range
Dim MaxCells As Long
Dim Result() As Variant
Dim R As Long
Dim C As Long

If (DataRange.Rows.Count > 1) And _
    (DataRange.Columns.Count > 1) Then
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' If DataRange is a two-dimensional array, fill
    ' it with #REF errors. We work with only
    ' single dimensional ranges.
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ReDim Result(1 To DataRange.Rows.Count, 1 To DataRange.Columns.Count)
    For R = 1 To UBound(Result, 1)
        For C = 1 To UBound(Result, 2)
            Result(R, C) = CVErr(xlErrRef)
        Next C
    Next R
    NoBlanks = Result
    Exit Function
End If

If (Application.Caller.Rows.Count > 1) And _
    (Application.Caller.Columns.Count > 1) Then
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ' If Application.Caller is a two-dimensional array, fill
    ' it with #REF errors. We work with only
    ' single dimensional ranges.
    '''''''''''''''''''''''''''''''''''''''''''''''''
    ReDim Result(1 To Application.Caller.Rows.Count, 1 To Application.Caller.Columns.Count)
    For R = 1 To UBound(Result, 1)
        For C = 1 To UBound(Result, 2)
            Result(R, C) = CVErr(xlErrRef)
        Next C
    Next R
    NoBlanks = Result
    Exit Function
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Get the greater of Application.Caller.Cells.Count
' and DataRange.Cells.Count. This is the size
' of the array we'll return. Sizing it to the
' maximum value prevents #N/A error from appearing
' in cells past the end of the array, because
' the array will always fill out the cells
' into which it was entered.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
MaxCells = Application.WorksheetFunction.Max( _
    Application.Caller.Cells.Count, DataRange.Cells.Count)

''''''''''''''''''''''''''''''''''''''''''''
' Resize the array to the proper size.
''''''''''''''''''''''''''''''''''''''''''''
ReDim Result(1 To MaxCells, 1 To 1)
''''''''''''''''''''''''''''''''''''''''''''
' Loop through DataRange and place non-blank
' cells at the front of the array.
''''''''''''''''''''''''''''''''''''''''''''
For Each Rng In DataRange.Cells
    If Rng.Value <> vbNullString Then
        N = N + 1
        Result(N, 1) = Rng.Value
    End If
Next Rng
''''''''''''''''''''''''''''''''''''''''''''
' Fill the remaining array elements with
' vbNullStrings so they don't appear
' as 0 on the worksheet.
''''''''''''''''''''''''''''''''''''''''''''
For N2 = N + 1 To MaxCells
    Result(N2, 1) = vbNullString
Next N2

'''''''''''''''''''''''''''''''''''''''''''
' If the formula was entered into a single
' row across several columns, Transpose the
' result array.
'''''''''''''''''''''''''''''''''''''''''''
If Application.Caller.Rows.Count = 1 Then
    NoBlanks = Application.Transpose(Result)
Else
    NoBlanks = Result
End If

End Function

Download a workbook illustrating these formulas and VBA code.