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
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
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.
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?