This Page: www.cpearson.com/excel/NoBlanks.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 27-Jan-2014

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Eliminating Blank Cells In A Range

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

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.

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. |