# Function to get constants of range

The following small function receives a range and returns it with only the cells that contain constants or Nothing if there's none.

Private Function GetConstants(ByVal Cells As Range) As Range
On Error GoTo ErrHandler
Set GetConstants = Intersect(Arg1:=Cells.SpecialCells(Type:=xlCellTypeConstants), Arg2:=Cells)
Exit Function
ErrHandler: If Err.Number <> 1004 Then Err.Raise Number:=Err.Number, Source:=Err.Source, Description:=Err.Description, HelpFile:=Err.HelpFile, HelpContext:=Err.HelpContext
End Function


If there's no constants in the range Cells, the Range.SpecialCells method throws the error:

Run-time error '1004':

Application-defined or object-defined error

The error handler ignores it and returns the default value Nothing.

The use of the Intersect method fixes the issue that occurs when the range is only one cell. It otherwise treats it as the whole sheet.

Question:

Is there any edge case not being handled or not in the best overall way?

Did I miss something, are the names ideal, should I check If Not TypeOf Cells Is Range Then Exit Function, or maybe use another way instead of Intersect and checking Err.Number <> 1004?

I don't see any issues with any "Edge Cases". Raising the Error, however, does severely limit the possible "Use Cases". The way it is written you can not test a series of ranges for constant values because it will throw an error. IMO, it would be more useful to have the function escape the error and return Nothing. In this way, you can create a customized Error Handler at the point of use. For example: If not GetConstants(rngA) is Nothing Then ... If not GetConstants(rngB) is Nothing ....