Excel VBA: Improve Excel Range Evaluation by Trimming Unused Cells

Passing too many cell references can easily make Excel slow or unresponsive. Although it’s convenient to use whole-column references — especially when you’re unsure of the data boundaries — this practice is not ideal. As users add or remove data, formulas must adapt dynamically, but referencing entire columns often includes thousands of empty cells, slowing down performance.

While built-in Excel functions handle this well by efficiently working within the used data range, custom VBA procedures and functions may not. They can struggle with performance due to inefficient data access and unnecessary evaluation of empty cells.

To address this, the MinifyRange function described in this article helps limit the number of cells processed by narrowing a large range down to only the cells that contain data. This lets your UDFs keep the flexibility of whole-column references but avoids evaluating unnecessary empty cells. It’s important to note that MinifyRange trims only the empty boundary cells, not the empty cells within the used data range.

Instructions

Create a New VBA Module

1. From the Developer ribbon in Excel, click Visual Basic.
2. In the Visual Basic for Applications window, go to Insert > Module.
3. Paste the following code into the new module and close the editor.

Option Explicit

Private Function FindFirstUsedCell(rng As Range) As Range
  Set rng = Intersect(rng, rng.Parent.UsedRange)
  Set FindFirstUsedCell = rng.Find("*", _
    Cells(rng.Row + rng.Rows.Count - 1, _
          rng.Column + rng.Columns.Count - 1), _
    xlValues, , xlByRows, xlNext)
End Function

Private Function FindLastUsedCell(rng As Range) As Range
  Set rng = Intersect(rng, rng.Parent.UsedRange)
  Set FindLastUsedCell = rng.Find("*", rng.Item(1), _
    xlValues, , xlByRows, xlPrevious)
End Function

Public Function MinifyRange(rng As Range) As Range
  Dim columnRange As Range
  Dim firstCell As Range
  Dim lastCell As Range
  Dim minRow As Long: minRow = 0
  Dim maxRow As Long: maxRow = 0
  Dim minCol As Long: minCol = 0
  Dim maxCol As Long: maxCol = 0
  Dim colIndex As Long

  If Not rng Is Nothing Then
    Set rng = Intersect(rng, rng.Parent.UsedRange)
    If Not rng Is Nothing Then
      For colIndex = rng.Column To rng.Column + rng.Columns.Count - 1
        Set columnRange = rng.Worksheet.Range( _
          rng.Worksheet.Cells(rng.Row, colIndex), _
          rng.Worksheet.Cells(rng.Row + rng.Rows.Count - 1, colIndex))

        Set firstCell = FindFirstUsedCell(columnRange)
        Set lastCell = FindLastUsedCell(columnRange)

        If Not firstCell Is Nothing Then
          If minRow = 0 Or firstCell.Row < minRow Then
            minRow = firstCell.Row
          End If
          If minCol = 0 Or firstCell.Column < minCol Then
            minCol = firstCell.Column
          End If
        End If

        If Not lastCell Is Nothing Then
          If maxRow = 0 Or lastCell.Row > maxRow Then
            maxRow = lastCell.Row
          End If
          If maxCol = 0 Or lastCell.Column > maxCol Then
            maxCol = lastCell.Column
          End If
        End If
      Next colIndex
    End If
  End If

  If minRow <> 0 And minCol <> 0 And maxRow <> 0 And maxCol <> 0 Then
    Set MinifyRange = rng.Worksheet.Range( _
      rng.Worksheet.Cells(minRow, minCol), _
      rng.Worksheet.Cells(maxRow, maxCol))
  Else
    Set MinifyRange = Nothing
  End If
End Function

Usage

The MinifyRange(range) function can be used directly in Excel formulas or in VBA code. It takes a single argument — a cell or range — and returns a trimmed version that excludes empty boundary rows and columns.

Suppose you have data in cells A1:H10. If you pass $B:$F as the input range, Excel sees this as 1,048,576 rows across five columns (5,242,880 cells). That’s a lot of unnecessary cells!

The MinifyRange function reduces this reference to only the part of the range that contains actual data. After trimming, only a smaller section — like C3:E8 (or 15 cells) — is evaluated

Results

To show how this helps, here’s a simple helper function:

Option Explicit

Public Function Loopy(rng As Range) As Long
  Dim cell As Range
  Dim cellCount As Long: cellCount = 0

  For Each cell In rng
    cellCount = cellCount + 1
  Next cell

  Loopy = cellCount
End Function

Without MinifyRange

=Loopy($D:$E)

This evaluates 2,097,152 cells.

With MinifyRange

=Loopy(MinifyRange($D:$E))

This evaluates only the 8 used cells (e.g., D3:E6), drastically improving performance.

Summary

Using whole-column references in Excel can severely impact performance by forcing evaluation of millions of cells. The MinifyRange function solves this by dynamically trimming the range to only used cells. This makes your formulas more efficient while keeping them flexible for dynamic data.