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.

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.

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.

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.

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.

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.

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

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