Sub x()
Dim r1 As Range, r2 As Range
Set r1 = Range("A1:J10")
On Error Resume Next
Set r2 = r1.SpecialCells(xlCellTypeConstants)
If r2 Is Nothing Then
Set r2 = r1.SpecialCells(xlCellTypeBlanks)
Else
Set r2 = Union(r2, r1.SpecialCells(xlCellTypeBlanks))
End If
On Error GoTo 0
If r2 Is Nothing Then
MsgBox "No non-formula cells found"
Else
MsgBox r2.Address
End If
End Sub