Faster Excel Calculations By Minimizing Range Boundaries

Passing too many cell references into a custom user-defined function (UDF) is the easiest way to make Excel unresponsive. While it’s very convenient to pass whole column references to functions, especially when data boundaries are unknown at runtime, it’s not recommended as a best practice. In other words, users will continue adding and removing data from a worksheet so formulas using that data must be flexible to accommodate the entire expected data set. However, the flexibility offered by whole column references may come with a lot of empty cells that must be evaluated.

Built-in Excel functions don’t usually suffer from this issue since they have efficient access to the data elements and they generally only evaluate the used range regardless of the number of empty cell references included in the function call. User-defined functions in VBA are more prone to become unresponsive due to inefficiencies in both data access and range evaluation as well as poor programming.

The MINIFY_RANGE function described in this post attempts to logically reduce the volume of data processed by a function while maintaining the flexibility of using whole column references. The goal of this function is to remove any empty boundary cells from a range reference so that subsequent functions and formula only need to process the used data range. This function does not remove empty cells within the identified used range.

Usage

Find_First_Used_Cell_In_Range(range)

The Find_First_Used_Cell_In_Range function returns the first cell containing a non-empty value found within the supplied range. If the supplied range contains multiple columns, then the function searches across all columns in a row before moving down to the next row. Internally, the function starts searching from the last cell in the range (ignoring any value in the last cell) and immediately cycles to the first cell cell.

Excel – Find First Used Cell In Range
Excel – Find First Used Cell In Range
ArgumentRequiredDescription
rangeYesReference to a single cell or range of cells to evaluate.

Find_Last_Used_Cell_In_Range(range)

The Find_Last_Used_Cell_In_Range function returns the last cell containing a non-empty value found within the supplied range. If the supplied range contains multiple columns, then the function searches across all columns in a row in reverse before moving up to the next row. Internally, the function starts searching from the first cell in the range (ignoring any value in the first cell) and immediately cycles to the last cell.

Excel – Find Last Used Cell In Range
Excel – Find Last Used Cell In Range
ArgumentRequiredDescription
rangeYesReference to a single cell or range of cells to evaluate.

MINIFY_RANGE(range)

The MINIFY_RANGE function returns a range where any empty boundary rows and columns are removed. To illustrate the algorithm, we have a worksheet containing data in the range A1:H10. This is considered the used range internally by Excel and highlighted in blue. The whole column reference $B:$F is passed to the MINIFY_RANGE function as shown by the selected cells.

Excel VBA – Used Range
Excel VBA – Used Range

The function first uses Intersect to remove any cells from the supplied range that are not part of the used range (blue shaded cells) in the overall worksheet. As a result of the Intersect, the interim range is reduced from $B:$F to $B1:$F10. While this represents a substantial reduction in cells to evaluate, we still have empty boundary cells in columns B and F as well as rows 1, 2, 8, 9, and 10.

Excel VBA – Post Intersect Range
Excel VBA – Post Intersect Range

Next, the function uses the Find_First_Used_Cell_In_Range and Find_Last_Used_Cell_In_Range functions to evaluate each column in the supplied range. The function begins with a zero-dimension range and then attempts to identify the first used cell and last used cell in each column. If those first and last used cells in the evaluation column are in a lower numbered row or column than prior evaluations, then the zero-dimension range is expanded until all columns are evaluated. The result identifies the used boundary as C3, C7, E3, and E8.

Passing the range $B:$F to MINIFY_RANGE results in the function returning the range C3:E7 (green shaded cells). The empty boundary cells (red shaded cells) are removed from the returned range.

Excel VBA – Final Minified Range
Excel VBA – Final Minified Range
ArgumentRequiredDescription
rangeYesReference to a single cell or range of cells to evaluate.

Creating the Functions

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert New Module
Excel VBA – Insert New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

To demonstrate the MINIFY_RANGE function, I have created a simple helper function named LOOPY that returns a count of each cell included in the supplied range argument.

Example 1 – Without MINIFY_RANGE

Two whole column references are include in the range. Without using the MINIFY_RANGE function, all 2,097,152 cells across the two columns (1,048,576 x 2) are evaluated by the LOOPY function.

=loopy($D:$E)

Example 2 – With MINIFY_RANGE

Two whole column references are include in the range. By using the MINIFY_RANGE function, only 8 cells across the two columns are evaluated by the LOOPY function. The MINIFY_RANGE function returns the range reference D3:E6.

=loopy(MINIFY_RANGE($D:$E))
Excel – Minified Range Result
Excel – Minified Range Result
Function LOOPY(rRange As Range) As Long
  Dim rCell As Range
  Dim lCount As Long: lCount = 0

  For Each rCell In rRange
    lCount = lCount + 1
  Next rCell

  LOOPY = lCount
End Function

Source Code

Option Explicit

Private Function Find_First_Used_Cell_In_Range(rRange As Range) As Range
  Set rRange = Intersect(rRange, rRange.Parent.UsedRange)

  Set Find_First_Used_Cell_In_Range = rRange.Find("*", Cells(rRange.Row + rRange.Rows.Count - 1, rRange.Column + rRange.Columns.Count - 1), xlValues, , xlByRows, xlNext)
End Function

Private Function Find_Last_Used_Cell_In_Range(rRange As Range) As Range
  Set rRange = Intersect(rRange, rRange.Parent.UsedRange)

  Set Find_Last_Used_Cell_In_Range = rRange.Find("*", rRange.Item(1), xlValues, , xlByRows, xlPrevious)
End Function

Function MINIFY_RANGE(rRange As Range) As Range
  Dim rColumnToEvaluate As Range
  Dim rFirstUsedCellInColumn As Range
  Dim rLastUsedCellInColumn As Range
  Dim lMinRow As Long: lMinRow = 0
  Dim lMaxRow As Long: lMaxRow = 0
  Dim lMinCol As Long: lMinCol = 0
  Dim lMaxCol As Long: lMaxCol = 0
  Dim lColumnIterator As Long

  If Not rRange Is Nothing Then
    Set rRange = Intersect(rRange, rRange.Parent.UsedRange)

    If Not rRange Is Nothing Then
      For lColumnIterator = rRange.Column To rRange.Column + rRange.Columns.Count - 1
        Set rColumnToEvaluate = rRange.Worksheet.Range(rRange.Worksheet.Cells(rRange.Row, lColumnIterator), rRange.Worksheet.Cells(rRange.Row + rRange.Rows.Count - 1, lColumnIterator))
        Set rFirstUsedCellInColumn = Find_First_Used_Cell_In_Range(rColumnToEvaluate)
        Set rLastUsedCellInColumn = Find_Last_Used_Cell_In_Range(rColumnToEvaluate)

        If Not rFirstUsedCellInColumn Is Nothing Then
          If lMinRow = 0 Or rFirstUsedCellInColumn.Row < lMinRow Then
            lMinRow = rFirstUsedCellInColumn.Row
          End If

          If lMinCol = 0 Or rFirstUsedCellInColumn.Column < lMinCol Then
            lMinCol = rFirstUsedCellInColumn.Column
          End If
        End If

        If Not rLastUsedCellInColumn Is Nothing Then
          If lMaxRow = 0 Or rLastUsedCellInColumn.Row > lMaxRow Then
            lMaxRow = rLastUsedCellInColumn.Row
          End If

          If lMaxCol = 0 Or rLastUsedCellInColumn.Column > lMaxCol Then
            lMaxCol = rLastUsedCellInColumn.Column
          End If
        End If
      Next lColumnIterator
    End If
  End If

  If lMinRow <> 0 And lMinCol <> 0 And lMaxRow <> 0 And lMaxCol <> 0 Then
    Set MINIFY_RANGE = rRange.Worksheet.Range(rRange.Worksheet.Cells(lMinRow, lMinCol), rRange.Worksheet.Cells(lMaxRow, lMaxCol))
  Else
    Set MINIFY_RANGE = Nothing
  End If
End Function

Counting Based on Background Color in Excel

As of Excel 2019, there are no built-in formulas to count cells based on background color (also known as fill color or interior color). The following user-defined functions (UDF) provide the capability to count all cells in a range with a particular background color.

Why would anyone need to count cells based on background color? There are usually better values or criteria available in the data set itself to count, right? Since you’ve found yourself reading this post, you must have a specific reason. For me, counting background color was needed to create an error checking dashboard. On a rather complicated Excel workbook, there were a number of different error checks throughout the workbook that would trigger conditional formatting when specific error conditions were met. Instead of creating additional helper columns to flag the error conditions, the conditional formatting rules turned a cell’s background color red if any error conditions were met. The dashboard view then used the COUNT_BACKGROUND_COLOR function to count all of these flagged errors (the red cells) and summarize the errors on an easy to monitor dashboard so that the errors could be corrected.

The two user-defined functions demonstrated in this post are GET_BACKGROUND_COLOR and COUNT_BACKGROUND_COLOR.

Usage

GET_BACKGROUND_COLOR(reference)

The GET_BACKGROUND_COLOR function evaluates a single cell and returns it’s color index. The color index is a numeric value corresponding to a particular color recognized by Excel.

ArgumentRequiredDescription
referenceYesReference to a single cell.

COUNT_BACKGROUND_COLOR(color index, range1, [range2], …)

The COUNT_BACKGROUND_COLOR function counts all cells in a specified range where interior color is set to the supplied color index.

ArgumentRequiredDescription
color indexYesNumeric value corresponding to a color; use xlNone for no color.
range1YesReference to a single cell or range of cells to evaluate.
[range2, …]NoAdditional references to a single cell or range of cells to include in the evaluation.

Known Limitations

If a large, used range is supplied to the COUNT_BACKGROUND_COLOR function, then Excel may become unresponsive. The function counts cells by evaluating each cell individually for the supplied color index. As an example, passing whole column references in a large data set may require evaluating 1,048,576 cells for each whole column. If whole column references are supplied to the function but the worksheet used range is mostly empty, then the function will ignore the empty cells and perform as expected.

Creating the Functions

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert New Module
Excel VBA – Insert New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

After adding red, yellow, or green fill colors to a few cells on an Excel worksheet, we can test the two new functions. In these examples, the colors are applied to cells in the E2:G16 range.

Example 1 – Red Background Color

The GET_BACKGROUND_COLOR function returns a value 3 which corresponds to the red fill color used on cell E3.

=GET_BACKGROUND_COLOR(E3)

The COUNT_BACKGROUND_COLOR function returns a value 13. Manually counting all red cells in the E2:G16 range confirms that there are 13 red filled cells.

=COUNT_BACKGROUND_COLOR(3,E2:G16)

Example 2 – Yellow Background Color

The GET_BACKGROUND_COLOR function returns a value 6 which corresponds to the yellow fill color used on cell E6.

=GET_BACKGROUND_COLOR(E6)

The COUNT_BACKGROUND_COLOR function returns a value 5. Manually counting all yellow cells in the E2:G16 range confirms that there are 5 yellow filled cells.

=COUNT_BACKGROUND_COLOR(6,E2:G16)

Example 3 – Green Background Color

The GET_BACKGROUND_COLOR function returns a value 43 which corresponds to the green fill color used on cell G6.

=GET_BACKGROUND_COLOR(G6)

The COUNT_BACKGROUND_COLOR function returns a value 6. Manually counting all green cells in the E2:G16 range confirms that there are 6 green filled cells.

=COUNT_BACKGROUND_COLOR(43,E2:G16)
Excel – Counts Based on Background Color
Excel – Counts Based on Background Color

Source Code

Option Explicit

Function GET_BACKGROUND_COLOR(rCell As Range) As Long
  If IsObject(rCell) Then
    If rCell.Cells.Count = 1 Then
      GET_BACKGROUND_COLOR = rCell.Interior.ColorIndex
      Exit Function
    End If
  End If

  GET_BACKGROUND_COLOR = CVErr(xlErrRef)
End Function

Function COUNT_BACKGROUND_COLOR(lBackgroundColor As Long, ParamArray data()) As Long
  Dim lParamArrayIterator As Long
  Dim vCellIterator As Variant
  Dim lCellCount As Long: lCellCount = 0

  For lParamArrayIterator = LBound(data) To UBound(data)
    If IsObject(data(lParamArrayIterator)) Then
      Set data(lParamArrayIterator) = Intersect(data(lParamArrayIterator), data(lParamArrayIterator).Parent.UsedRange)

      For Each vCellIterator In data(lParamArrayIterator)
        If vCellIterator.Interior.ColorIndex = lBackgroundColor Then
          lCellCount = lCellCount + 1
        End If
      Next vCellIterator
    End If
  Next lParamArrayIterator

  COUNT_BACKGROUND_COLOR = lCellCount
End Function

Concatenate Ranges, Arrays, and Values Using Excel VBA

If you have Office 2019 or a subscription to Microsoft 365, then you have access to a useful Excel function named TEXTJOIN. This function concatenates text across ranges, arrays, and individual values and combines them into a single cell value. This function eliminates a multi-step process that typically involves concatenating individual cells across a range, copying/pasting the results to a text editor, and then removing line breaks.

As an alternative for those who do not have access to TEXTJOIN, the following is a user-defined function (UDF) named CONCATENATE_RANGE that mimics most of the TEXTJOIN functionality with similar performance.

Usage

CONCATENATE_RANGE(delimiter, ignore_empty, row_priority, text1, [text2], …)

The CONCATENATE_RANGE function evaluates each of the supplied ranges, arrays, and values and concatenates (combines) the identified values together into a single string separated by the supplied delimiter.

ArgumentRequiredDescription
delimiterYesA text string used to separate each concatenated value. The delimiter may be an empty string, one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_emptyYesIf TRUE, the function will ignore any empty values.
row_priorityYesIf TRUE, the function will first concatenate each value in a row first (moving across columns); if FALSE, the function will first concatenate each value in a column (moving down rows).
text1YesValue to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.
[text2, …]NoAdditional values to concatenate. Parameter may be a text string, a cell range, or an array. If a number is supplied, it will be treated as text.

Known Limitations

If the result exceeds 32767 characters, including the delimiter, the function returns the #VALUE! error. This is due to the inherent cell length limitation in Excel.

The function also does not work on 3D ranges (ranges that span multiple worksheets).

Creating the Function

Step 1 – Open Visual Basic

From the Developer ribbon in Excel, click Visual Basic.

The Microsoft Visual Basic for Applications window opens.

Step 2 – Insert New Module

From the Insert menu, click Module.

Excel VBA – Insert Module
Excel VBA – Insert Module
Excel VBA – New Module
Excel VBA – New Module

Step 3 – Add Code

Add the below code to the newly created module.

Step 4 – Close Visual Basic

Close the Microsoft Visual Basic for Applications window.

Result

All results from the following examples are displayed in the screenshot below.

Example 1 – TEXTJOIN

This example is used as the baseline to compare with the result from CONCATENATE_RANGE on the same data set.

=TEXTJOIN(", ",TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 2 – CONCATENATE_RANGE (row priority)

This example demonstrates that the result from CONCATENATE_RANGE matches the result from TEXTJOIN on the same data set.

=CONCATENATE_RANGE(", ", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 3 – CONCATENATE_RANGE (column priority)

When compared with Example 2, column priority sequences the resulting string by each entry in a column first.

=CONCATENATE_RANGE(", ", TRUE, FALSE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 4 – CONCATENATE_RANGE (row priority, include empty)

Similar result as Example 2 except empty values are included in the result.

=CONCATENATE_RANGE(", ", FALSE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)

Example 5 – CONCATENATE_RANGE (row priority, no delimiter)

Similar result as Example 2 except no delimiter is used to separate values.

=CONCATENATE_RANGE("", TRUE, TRUE,E1:F11,{"k",11;"l",12;"m",13;"n",14},"o",15,E13)
Excel – CONCATENATE_RANGE Examples
Excel – CONCATENATE_RANGE Examples

Source Code

Option Explicit

Function CONCATENATE_RANGE(delimiter As String, ignore_empty As Boolean, row_priority As Boolean, ParamArray data()) As String
  Dim arrayData() As Variant
  Dim lParamArrayIterator As Long
  Dim vDataArrayIterator As Variant
  Dim sResult As String: sResult = ""

  For lParamArrayIterator = LBound(data) To UBound(data)
    If IsObject(data(lParamArrayIterator)) Or IsArray(data(lParamArrayIterator)) Then
      If IsObject(data(lParamArrayIterator)) Then
        If data(lParamArrayIterator).Cells.Count = 1 Then
          ReDim arrayData(1 To 1, 1 To 1)
          arrayData(1, 1) = data(lParamArrayIterator).Value2
        Else
          arrayData = data(lParamArrayIterator).Value2
        End If
      Else
        arrayData = data(lParamArrayIterator)
      End If

      If row_priority Then
        arrayData = Application.WorksheetFunction.Transpose(arrayData)
      End If

      For Each vDataArrayIterator In arrayData
        If ((ignore_empty And Not IsEmpty(vDataArrayIterator)) Or (Not ignore_empty)) Then
          sResult = sResult & vDataArrayIterator & delimiter
        End If

        If Len(sResult) > 32767 Then
          CONCATENATE_RANGE = CVErr(xlErrValue)
        End If
      Next vDataArrayIterator
    Else
      If ((ignore_empty And Not IsEmpty(data(lParamArrayIterator))) Or (Not ignore_empty)) Then
        sResult = sResult & data(lParamArrayIterator) & delimiter
      End If

      If Len(sResult) > 32767 Then
        CONCATENATE_RANGE = CVErr(xlErrValue)
      End If
    End If
  Next lParamArrayIterator

  If (Len(sResult) >= Len(delimiter)) Then
    sResult = Left(sResult, Len(sResult) - Len(delimiter))
  End If

  CONCATENATE_RANGE = sResult
End Function