How to combine two SpecialCells CellTypes?

[Stacy35216]

Hi,

I'm looking to create a range that contains all the constant AND
formula cells in a selection. I've tried
'selection.SpecialCells(xlConstants) and
selection.SpecialCells(xlFormulas)' , but that doesn't work because
there may be only one type of cell in the selection - constants or
formulas - and then instead of combining a range with a null set, Excel
throws an error because there are no cells in one of the sets.
It seems there should be an easy way to do this but I haven't found one
online or in any of my books and I'm growing frustrated. Please help if
you have any ideas!

Allison

 

[Ron de Bruin]

Maybe you can use this

Sub test()
Dim rng As Range
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("A1:C25").SpecialCells(xlConstants)
Set rng2 = Range("A1:C25").SpecialCells(xlFormulas)
Set rng = Application.Union(rng1, rng2)

rng.Select
End Sub

 

[Norman Jones]

Hi Allison,

Try something like:

Sub Tester()
Dim Rng1 As Range
Dim Rng2 As Range
Dim rng3 As Range
Dim mySelection As Range

If Selection.Count = 1 Then
Set mySelection = ActiveSheet.UsedRange
Else
Set mySelection = Selection
End If

On Error Resume Next
Set Rng1 = mySelection.SpecialCells(xlCellTypeConstants)
Set Rng2 = mySelection.SpecialCells(xlCellTypeFormulas)

If Rng1 Is Nothing Then
Set Rng1 = Rng2
ElseIf Rng2 Is Nothing Then
Set Rng2 = Rng1
End If

Err.Clear

On Error Resume Next
Set rng3 = Union(Rng1, Rng2)
If Err.Number <> 0 Then
MsgBox "No constants or formulae found!!"
End If

rng3.Select
On Error GoTo 0

End Sub