Union of possibly empty ranges

[Jerry W. Lewis]

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

 

[Barb Reinhardt]

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

 

[Dave Peterson]

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

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.

 

[JLGWhiz]

Sure you didn't forget one, Dave?<g>

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

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]

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]

I don't think you missed any easy way of doing this, but I don't think you
should be using isempty() to check what may be a multi-cell range.

Checking isempty() with a single cell is ok. But that actually tells you if the
cell is empty--not a problem when you're looking at constants or formulas, but
could be a problem with cells with comments.

 

[Jerry W. Lewis]

Sorry if I was not clear; I agreed that IsEmpty() did not do what I wanted
while "Is Nothing" did; therefore "Is Nothing" is the approach I will be
using. Thank you for pointing me in the right direction.

Jerry

I don't think you missed any easy way of doing this, but I don't think you
should be using isempty() to check what may be a multi-cell range.

Checking isempty() with a single cell is ok. But that actually tells you if the
cell is empty--not a problem when you're looking at constants or formulas, but
could be a problem with cells with comments.

....

 

[Dave Peterson]

Oops. I did misunderstand.

But glad you have a solution that works.