The "Is Nothing" test for ranges (also suggested by Barb) nicely avoids the
difficulty of distinguishing no cells from empty cells. Thanks.

I prefer my sequential construction to doing all the combining at the end,
because it makes it easier to add or remove conditions without rethinking the
architecture.

I take it that I did not overlook some easy way to directly construct the
combined range without all the fuss?

I'm glad that neither of you were confused by the typo of
IsEmpty(Count)
which was supposed to have been
Not IsEmpty(area2)
Sorry about that.

Thanks again for the feedback,
Jerry

"Dave Peterson" wrote:

I'd use 3 range variables and combine them into 1 big one.

Option Explicit
Sub testme()

Dim RngConst As Range
Dim RngForm As Range
Dim RngComm As Range
Dim RngTot As Range

Set RngConst = Nothing
Set RngForm = Nothing
Set RngComm = Nothing
Set RngTot = Nothing

With ActiveSheet
On Error Resume Next
Set RngConst = .Cells.SpecialCells(xlCellTypeConstants)
Set RngForm = .Cells.SpecialCells(xlCellTypeFormulas)
Set RngComm = .Cells.SpecialCells(xlCellTypeComments)
On Error GoTo 0
End With

If RngConst Is Nothing Then
If RngForm Is Nothing Then
If RngComm Is Nothing Then
'do nothing
Else
Set RngTot = RngComm
End If
Else
'has formulas
If RngComm Is Nothing Then
Set RngTot = RngForm
Else
Set RngTot = Union(RngForm, RngComm)
End If
End If
Else
'has constants
If RngForm Is Nothing Then
If RngComm Is Nothing Then
Set RngTot = RngConst
Else
Set RngTot = Union(RngConst, RngComm)
End If
Else
'has formulas, too
If RngComm Is Nothing Then
Set RngTot = Union(RngConst, RngForm)
Else
Set RngTot = Union(RngConst, RngForm, RngComm)
End If
End If
End If

If RngTot Is Nothing Then
MsgBox "nothing found"
Else
MsgBox RngTot.Address
End If

End Sub

======
A long time ago, I saw a post by Tom Ogilvy that was pretty neat. Someone asked
a similar question and his solution was to insert a new worksheet (or a
worksheet in a new workbook) and use that to help.

Something like:

Option Explicit
Sub testme2()

Dim ActWks As Worksheet
Dim TmpWks As Worksheet
Dim Addr As String
Dim RngTot As Range

Set ActWks = ActiveSheet
Set TmpWks = Workbooks.Add(1).Worksheets(1)
Set RngTot = Nothing

With ActWks
On Error Resume Next
Addr = ""
Addr = .Cells.SpecialCells(xlCellTypeConstants).Address
If Addr = "" Then
'skip it
Else
TmpWks.Range(Addr).Value = 1
End If

Addr = ""
Addr = .Cells.SpecialCells(xlCellTypeFormulas).Address
If Addr = "" Then
'skip it
Else
TmpWks.Range(Addr).Value = 1
End If

Addr = ""
Addr = .Cells.SpecialCells(xlCellTypeComments).Address
If Addr = "" Then
'skip it
Else
TmpWks.Range(Addr).Value = 1
End If

Addr = ""
Addr = TmpWks.Cells.SpecialCells(xlCellTypeConstants).Add ress

TmpWks.Parent.Close savechanges:=False

If Addr = "" Then
'do noting
Else
Set RngTot = .Range(Addr)
End If

End With

If RngTot Is Nothing Then
MsgBox "nothing found"
Else
MsgBox RngTot.Address
End If

End Sub

I thought it was pretty slick.

And if you decide to add more requirements, it's easier (I think) to modify.



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

--

Dave Peterson