Reciprocal range?

  • Thread starter Thread starter Charley Kyd
  • Start date Start date
C

Charley Kyd

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.
 
Charley Kyd said:
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
Click to expand...



Charley,

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


Hope this helps,

Mike
 
Nuts.

Charley

Tom Ogilvy said:
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.
Click to expand...
 
Well, that sure was a long time ago. Ahhh! :>0)

Here is what I use now. Most likely, cell validation is not being used, so
this avoids having to add a sheet if not required. Since the program
Mathematica calls this type of operation a "Complement", I use the same
name.

Function Complement(BigRng As Range, RemoveRng As Range) As Range
' By: Dana DeLouis
Dim Remember As Worksheet
On Error Resume Next

Set Complement = Cells.SpecialCells(xlCellTypeAllValidation)

If Not Complement Is Nothing Then
Set Remember = ActiveSheet
Worksheets.Add
End If

BigRng.Validation.Add Type:=xlValidateInputOnly
RemoveRng.Validation.Delete
Set Complement = BigRng.SpecialCells(xlCellTypeAllValidation)

If Not Remember Is Nothing Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Remember.Activate
Else
Cells.Validation.Delete
End If
End Function


Sub TestIt()
Debug.Print Complement([B1:B6], [B2,B4]).Address
End Sub

returns:
$B$1,$B$3,$B$5:$B$6
 
Back
Top