Search code examples
excelvbaarray-formulas

find all array formulas in an excel worksheet

is there a way to find all array formulas in a given excel spreadsheet?

Solution

  • Take a look at this example. Hope that it helps. 

    Sub array_formula()
Dim rRange As Range, cell As Range
Dim tot As Integer
Set rRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    For Each cell In rRange
        If cell.HasArray Then
            MsgBox cell.Address & " " & cell.formula
            tot = tot + 1
         End If
     Next cell
MsgBox "total number of array formula: " & tot
End Sub