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.
| Argument | Required | Description |
|---|---|---|
| range | Yes | Reference 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.
| Argument | Required | Description |
|---|---|---|
| range | Yes | Reference 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.
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.
| Argument | Required | Description |
|---|---|---|
| range | Yes | Reference 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.
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 FunctionSource 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
