Have you tried something like this. I have a long macro running so can't
check it in Excel.

Dim Area as range

Set Area = Nothing
on error resume next
Set Area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants )
on error goto 0
If Area is nothing then
...do something else
end if

HTH,
Barb Reinhardt

"Jerry W. Lewis" wrote:

I need to construct a Range object that includes all cells with values,
formulas, or comments. That seems surprisingly hard to do, because
SpecialCells throws an error if no cells of the specified type are found, and
Union fails if any of the component ranges are empty. The following code
works, but it feels like I am working way to hard. Any suggestions?

Jerry

Sub tryit()
' make Range object containing all cells with values, formulas, or comments
On Error Resume Next
Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeConstants )
If IsEmpty(area) Then
Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
Else
Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
If IsEmpty(Count) Then Set area = Union(area, area2) ' Union()
fails if either range is empty
area2 = Empty
End If
If IsEmpty(area) Then
Set area = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
Else
Set area2 = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
If area2.Count 0 Then Set area = Union(area, area2) ' .Count is
only reliable way to distinguish no cells from empty cells with comments
End If
area2 = 0
area2 = area.Count ' if area2 = 0, then no cells were found
On Error GoTo 0: Err.Clear
area.Select ' for easy verification that the snippet worked
End Sub