Hi KeepITcool,

I have amended this function taking your comments into account. More
specifically,

WS argument can be removed.

Agreed - I actually intended the WS variable to refer to a variable sheet
but manged to fall between two stools.

if rngB is nothing then set rngB=rngA.Parent.usedrange

Yes - Happily incorporated.

(rngB and RngA must be on the same sheet for a union to work anyway)

Yes - see WS point above.

also I want to build in some checks

in order not to destroy existing Validation...

I agree that this is necessary. I have amended the function to build an
array to store all possible validation variables. Once the function has
determined the RngNot range, the validation data is restored to any
validation cells. I hope that I have caught all possible variables.

if there is no intersect

An On Error Resume ... Goto added to catch this.

if rngA iss within rngB (or vice versa)

I looked at this and felt that no special action was required, Since,
however, you have specifically raised the point, you may see more here than
I did after my , admittedly, somemewhat cursory, consideration.

I think that there is (at leat) one futher point to consider: The 8192
non-contiguous cells limitation which, IIR, applies to pre-xl2002 . I
suppose that the logical step would be to adopt an
iI Intersect(RngA, RngB).Areas.Count 8191 Then
Break rnage into acceptable chunks & loop
End If
appoach.

I wanted to think about this however, not least because, in my testing, the
limit appeared to come into effect close to but definately *before* the
8192. Given other calls on my time, i was unable to rigorously test how far
(if at all) this phenomenon was
due to subtleties of my test parameters or simply error/oversight on my
part.

In any event, this is my revised code:

Function RngNot(RngA As Range, Optional RngB As Range) As Range
'---------------------------------------------
' Using Dave Peterson's interpretation of Tom Ogilvy's
' scratch sheet idea
' Adapted to replace the scratchsheet using Dana DeLouis's
' Validation idea
' Adapted as a function
' Amended to satisfy the need (pointed out by KeepITcool)
' to restore original validation - Validation values passed
' to and from an array
' Amended to add Non-Intersection error handling (KeepITcool)
'---------------------------------------------
Dim Rng As Range, cell As Range, i As Long

If RngB Is Nothing Then Set RngB = RngA.Parent.UsedRange

On Error Resume Next
Set Rng = Union(RngA, RngB).SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0

If Not Rng Is Nothing Then
ReDim arr(1 To Rng.Cells.Count, 1 To 14)
i = 0
For Each cell In Rng
i = i + 1
With cell.Validation
arr(i, 1) = cell.Address
arr(i, 2) = .Type
arr(i, 3) = .AlertStyle
arr(i, 4) = .Operator
arr(i, 5) = .Formula1
arr(i, 6) = .Formula2
arr(i, 7) = .ErrorMessage
arr(i, 8) = .ErrorTitle
arr(i, 9) = .IgnoreBlank
arr(i, 10) = .InputMessage
arr(i, 11) = .InputTitle
arr(i, 12) = .ShowError
arr(1, 13) = .ShowInput
arr(1, 14) = .InCellDropdown
End With
Next cell

Rng.Validation.Delete
End If

Union(RngA, RngB).Validation.Add 0, 1

On Error Resume Next
Intersect(RngA, RngB).Validation.Delete
On Error GoTo 0
Set RngNot = Union(RngA, RngB). _
SpecialCells(xlCellTypeAllValidation)
RngNot.Validation.Delete
If Not Rng Is Nothing Then
For i = LBound(arr) To UBound(arr)
With Range(arr(i, 1)).Validation
.Add Type:=arr(i, 2), AlertStyle:=arr(i, 3), _
Operator:=arr(i, 4), Formula1:=arr(i, 5), _
Formula2:=arr(i, 6)
.ErrorMessage = arr(i, 7)
.ErrorTitle = arr(i, 8)
.IgnoreBlank = arr(i, 9)
.InputMessage = arr(i, 10)
.InputTitle = arr(i, 11)
.ShowError = arr(i, 12)
.ShowInput = arr(1, 13)
.InCellDropdown = arr(1, 14)
End With
Next i
End If
End Function

---
Regards,
Norman