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.


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 like the use of Intersect to handle single cell ranges.

You should avoid using Cells as variable name because it could easily be confused with the built in Cells collection. By contrast Range As Range could be used because there is no default Range value. Personally, I use Source and Target to name my ranges. rSource and **rTarget would generally be considered better names but I think that the context in which they are used is descriptive enough.

The use of an Error Handler here is just fluff. All it does is just throw the same error that would be thrown without it. You should use Error Handlers to clean up the objects and reset the "Application State" when something goes wrong.

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.