Eliminating Blank Cells In A Range

This page describes formulas and VBA functions to remove blank cells from a range.

Introduction

It is not uncommon that you have a range of data containing both values and blank cells and you want to eliminate the blank cells. This page describes worksheet array formulas and VBA code to create a new range that contains only the non-blank elements of the original range.

Due to the way array formulas work, it is necessary that the original range and the new range be referenced by a defined name rather than directly by cell references. We will use BlanksRange to refer to the original range that contains both values and blanks, and we will use NoBlanksRange to reference the new range that contains only the non-blank values of the original range.

Worksheet Formulas

The image to the left illustrates a range named BlanksRange that contains a combination of values and blank cells. Although the values are in alphabetical order, this is by no means necessary. It is for illustration only. The values will be extracted and will appear in the no-blanks range in the order in which the appear in the original data.

To use the formula, paste it into the first cell of NoBlanksRange and then copy it down to fill that range. The NoBlanksRange should have as many rows as BlanksRange. Any unused cells in BlanksRange will contain empty values. 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, but you do not array enter it into the entire range at once. Array enter the formula into the first cell of NoBlanksRange and then fill down to the last cell of NoBlanksRange. The formula is:

=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 formula above is split into several lines for readability. In practice, it should be entered as a single line. A simpler method is available in Excel 2007 and later versions, using the IFERROR function.

=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")

Enter this formula into the first cell of NoBlanksRange and copy it down through the last cell of NoBlanksRange. Like the other formulas, this is an array formula, so enter it with CTRL SHIFT ENTER rather than just ENTER. This formula is for extracting the non-blank elements to a vertical range -- a range in a single column that spans several rows. If you want the results in a single row spanning several columns, use the following array formula, where the result range is named NoBlanksRow.

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

Array enter this formula into the first cell of NoBlanksRow and fill to the right through the last cell of NoBlanksRow.

VBA Function

If the formulas above seem overly complex, you might want to opt for a much simpler VBA function. The NoBlanks function is shown below.

Function NoBlanks(RR As Range) As Variant
    Dim Arr() As Variant
    Dim R As Range
    Dim N As Long
    Dim L As Long
    If RR.Rows.Count > 1 And RR.Columns.Count > 1 Then
        NoBlanks = CVErr(xlErrRef)
        Exit Function
    End If
    
    If Application.Caller.Cells.Count > RR.Cells.Count Then
        N = Application.Caller.Cells.Count
    Else
        N = RR.Cells.Count
    End If
    
    ReDim Arr(1 To N)
    N = 0
    For Each R In RR.Cells
        If Len(R.Value) > 0 Then
            N = N + 1
            Arr(N) = R.Value
        End If
    Next R
    For L = N + 1 To UBound(Arr)
        Arr(L) = vbNullString
    Next L
    ReDim Preserve Arr(1 To L)
    If Application.Caller.Rows.Count > 1 Then
        NoBlanks = Application.Transpose(Arr)
    Else
        NoBlanks = Arr
    End If
End Function

This code does not require the use of any defined names. Simply array enter the formula NoBlanks into the entire range that is to get the results, passing to the function the range from which the blank elements are to be extracted. To array enter the formula into all the cells, first select the entire range that is to receive the results, enter =NoBlanks(A1:A10) in the first cell and press CTRL SHIFT ENTER. The code is written to be entered into a single column spanning several rows or a single row spanning several columns. If the number of rows and the number of columns of the input range are both greater than 1, the function will return a #REF error. The function will orient itself so that there is no difference between entering it into a row or into a column. Moreover, if the function is called from a range with more elements than there are non-blank elements, the results at the end of the result list will filled out with empty string to the full length of cells into which it was entered.

You can download the workbook file with all the example formulas and code on this page.

-->

This page last updated: 5-February-2011.