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