Suppose I have two ranges:

AllChildren = $B$1:$B$6
AllSons = $B$2, $B$4

And suppose that the children who aren't sons are daughters. **Without
looping** is there a way to return the range...
$B$1, $B$3, $B$5:$B$6
....for the daughters?

Thanks.

Charley

There is no built in support for it.

Sub ReciprocalRange()
Dim AllChildren As String, AllSons As String
Dim allDaughters As String, sh As Worksheet
Dim sh1 As Worksheet, rng As Range
Dim rng1 As Range, rng2 As Range
Application.ScreenUpdating = False
AllChildren = "$B$1:$B$6"
AllSons = "$B$2,$B$4"

Set sh = ActiveSheet
Set sh1 = Worksheets.Add
Set rng = Range(AllChildren)
Set rng1 = Range(AllSons)

rng.Value = 1
rng1.ClearContents
Set rng2 = rng.SpecialCells(xlConstants, xlNumbers)
allDaughters = rng2.Address
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
sh.Activate
Application.ScreenUpdating = True
MsgBox allDaughters

End Sub

But it would probably be just as easy to loop.

--
Regards,
Tom Ogilvy


"Charley Kyd" wrote in message
...
Suppose I have two ranges:

AllChildren = $B$1:$B$6
AllSons = $B$2, $B$4

And suppose that the children who aren't sons are daughters. **Without
looping** is there a way to return the range...
$B$1, $B$3, $B$5:$B$6
...for the daughters?

Thanks.

Charley

Nuts.

Charley

"Tom Ogilvy" wrote in message
...
There is no built in support for it.

Sub ReciprocalRange()
Dim AllChildren As String, AllSons As String
Dim allDaughters As String, sh As Worksheet
Dim sh1 As Worksheet, rng As Range
Dim rng1 As Range, rng2 As Range
Application.ScreenUpdating = False
AllChildren = "$B$1:$B$6"
AllSons = "$B$2,$B$4"

Set sh = ActiveSheet
Set sh1 = Worksheets.Add
Set rng = Range(AllChildren)
Set rng1 = Range(AllSons)

rng.Value = 1
rng1.ClearContents
Set rng2 = rng.SpecialCells(xlConstants, xlNumbers)
allDaughters = rng2.Address
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
sh.Activate
Application.ScreenUpdating = True
MsgBox allDaughters

End Sub

But it would probably be just as easy to loop.

--
Regards,
Tom Ogilvy


"Charley Kyd" wrote in message
...
Suppose I have two ranges:

AllChildren = $B$1:$B$6
AllSons = $B$2, $B$4

And suppose that the children who aren't sons are daughters. **Without
looping** is there a way to return the range...
$B$1, $B$3, $B$5:$B$6
...for the daughters?

Thanks.

Charley

"Charley Kyd" wrote in message
...
Suppose I have two ranges:

AllChildren = $B$1:$B$6
AllSons = $B$2, $B$4

And suppose that the children who aren't sons are daughters.
**Without
looping** is there a way to return the range...
$B$1, $B$3, $B$5:$B$6
...for the daughters?

Thanks.

Charley




Charley,

http://tinyurl.com/2tfpb contains a couple examples that take
advantage of the SpecialCells method.


Hope this helps,

Mike

Thanks, Mike.

Charley

"Mike" wrote in message
news:7ho2c.27287$Bz3.19159@okepread05...
"Charley Kyd" wrote in message
...
Suppose I have two ranges:

AllChildren = $B$1:$B$6
AllSons = $B$2, $B$4

And suppose that the children who aren't sons are daughters.
**Without
looping** is there a way to return the range...
$B$1, $B$3, $B$5:$B$6
...for the daughters?

Thanks.

Charley




Charley,

http://tinyurl.com/2tfpb contains a couple examples that take
advantage of the SpecialCells method.


Hope this helps,

Mike