If you only want numbers (not text), replace

Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants)

with

Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants, xlNumbers)


In article ,
JE McGimpsey wrote:

one way:

Public Sub ListConstants()
Dim wsSheet As Worksheet
Dim rCell As Range
Dim rDest As Range
Dim rConstants As Range
Dim nNumSheets As Long
Dim i As Long

nNumSheets = Sheets.Count
With Worksheets.Add(After:=Sheets(nNumSheets))
.Name = "Constants"
With .Range("A1:C1")
.Value = Array("Sheet", "Cell", "Value")
.Font.Bold = True
End With
Set rDest = .Range("A2")
End With
For i = 1 To nNumSheets
On Error Resume Next
Set rConstants = Worksheets(i).Cells.SpecialCells( _
xlCellTypeConstants)
On Error GoTo 0
If Not rConstants Is Nothing Then
For Each rCell In rConstants
With rCell
rDest.Value = .Parent.Name
rDest(1, 2).Value = .Address(False, False)
rDest(1, 3).Value = .Value
End With
Set rDest = rDest(2, 1)
Next rCell
End If
Next i
End Sub