get the cells that has no formula

[Svgmassive]

i would like to get to set a range based on cells that has no formula in a range how can i achieve this.thanks

 

[VoG]

Try

Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)

 

[shg]

Messier than you'd think.

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