Dynamic Array Spill Resolver in VBA

Overview

Excel VBA Dynamic Arrays

Dynamic arrays in Excel 365 are powerful, but #SPILL! errors occur when the spill range is obstructed by existing data. These macros automatically detect and resolve #SPILL errors by inserting rows or columns to create the space dynamic arrays need.

Special_Select_Dynamic_Arrays highlights all spill ranges in your selection. Loop_through_Sheet and Insert_Until_No_Spill go further by automatically fixing #SPILL errors across the entire workbook, inserting rows and columns where needed.

How It Works

Special Select: Scans the selection for cells with HasSpill = True and selects their full SpillingToRange, making it easy to see where dynamic arrays are outputting data
Spill Dimension Detection: Temporarily moves the spilling formula to a blank sheet to measure its true output dimensions, then moves it back
Smart Insertion: Checks if columns or rows are obstructed and inserts only what is needed, preferring column insertion for horizontal spills and row insertion for vertical ones
Workbook-Wide Loop: Iterates through all sheets repeatedly until no #SPILL errors remain, with a safety prompt every 100 iterations to prevent runaway execution

The VBA Code

Sub Special_Select_Dynamic_Arrays()
    Dim Rng As Range, Cel As Range
    Set Rng = Selection
    Dim Dyn_Arr_Rng As Range
    
    On Error GoTo Skip
    For Each Cel In Rng.SpecialCells(xlCellTypeFormulas)
        If Cel.HasSpill Then
            If Dyn_Arr_Rng Is Nothing Then
                Set Dyn_Arr_Rng = Cel.SpillingToRange
            Else
                Set Dyn_Arr_Rng = Union(Dyn_Arr_Rng, Cel.SpillingToRange)
            End If
        End If
    Next
    
    If Not Dyn_Arr_Rng Is Nothing Then
        Dyn_Arr_Rng.Select
    Else
Skip:
        MsgBox "No dynamic arrays found in the selection."
    End If
End Sub

Sub Loop_through_Sheet()
    ' Iterates all sheets, resolving #SPILL errors by inserting rows/columns
    Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")
    Dim Wks As Variant
    
    For Each Wks In ActiveWorkbook.Sheets
        Dic.Add Wks.Name, Wks.Name
    Next Wks

    Dim i As Long: i = 0
    Do While Dic.Count <> 0
        Dim Cur_Wks As String: Cur_Wks = Dic.Keys()(i Mod Dic.Count)
        Worksheets(Cur_Wks).Activate
        Application.StatusBar = "Checking #SPILL in: " & Cur_Wks
        i = i + 1
        
        Insert_Until_No_Spill
        
        Dim Err_Rng As Range: Set Err_Rng = Nothing
        On Error Resume Next
        Set Err_Rng = Get_Spill_Range()
        On Error GoTo 0
        
        If Err_Rng Is Nothing Then Dic.Remove Cur_Wks
        
        If (i Mod 100) = 0 Then
            If MsgBox(i & " iterations. Cancel?", vbOKCancel) = vbOK Then Exit Sub
        End If
    Loop
    Application.StatusBar = False
End Sub

Sub Insert_Until_No_Spill()
    ' For current sheet: finds #SPILL errors and inserts rows/columns to make room
    Dim Err_Rng As Range: Set Err_Rng = Get_Spill_Range()
    If Err_Rng Is Nothing Then Exit Sub
    
    Dim a As Long, b As Long
    For a = 1 To Err_Rng.Areas.Count
        Dim Spl_Cel As Range: Set Spl_Cel = Err_Rng.Areas(a).Cells(1)
        ' Temporarily move formula to blank sheet to measure spill dimensions
        ' Then insert rows/columns as needed to clear the obstruction
        Insert_Rows_Columns Spl_Cel, Row_Cnt, Col_Cnt
    Next a
End Sub

Final Thoughts

This tool is essential when migrating legacy workbooks to Excel 365 dynamic arrays. Instead of manually clearing cells around each spilling formula, the resolver automatically makes room, letting you focus on building better formulas.