How to combine two SpecialCells CellTypes?
 

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


--
Stacy35216
------------------------------------------------------------------------
Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952
View this thread: http://www.excelforum.com/showthread...hreadid=274326

How to combine two SpecialCells CellTypes?
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Stacy35216" wrote in message ...

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


--
Stacy35216
------------------------------------------------------------------------
Stacy35216's Profile: http://www.excelforum.com/member.php...o&userid=15952
View this thread: http://www.excelforum.com/showthread...hreadid=274326

How to combine two SpecialCells CellTypes?
 

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


---
Regards,
Norman



"Stacy35216" wrote in message
...

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


--
Stacy35216
------------------------------------------------------------------------
Stacy35216's Profile:
http://www.excelforum.com/member.php...o&userid=15952
View this thread: http://www.excelforum.com/showthread...hreadid=274326