Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specialcells | Charts and Charting in Excel | |||
SpecialCells(xlCellTypeLastCell) | Excel Programming | |||
SpecialCells and UsedRange | Excel Programming | |||
SpecialCells(xlCellTypeFormulas) | Excel Programming | |||
AutoFilter /specialcells | Excel Programming |