nbsp; Eliminating Blank Cells From Lists
This page has been replaced. Click here if you are not automatically redirected. You can use a formula to return only the nonblank 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) The first N rows of NoBlanksRange will contain the N nonblank 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
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 nonblank values ' in DataRange. The function must be arrayentered 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 nonblank ' 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 twodimensional 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 twodimensional 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 twodimensional 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 twodimensional 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 nonblank ' 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
