I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks

Include your efforts to date
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Michael" wrote in message
...
I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks

To get the ball rolling you could adapt something along these lines:


VBA Code:
--------------------


Private Sub Worksheet_Change(ByVal Target As Range)
Dim theRange As Range
If Not Intersect(Target, Range("$B$4:$E$14")) Is Nothing And Not IsEmpty(Target) Then
Set theRange = Range("$B$4:$E$14")
If Application.CountIf(theRange, Target.Value) 1 Then
Set c = Target
Do
Set c = theRange.Find(Target.Value, c)
If c.Address < Target.Address Then c.ClearContents
Loop Until c.Address = Target.Address
End If
End If
End Sub
--------------------






Michael;716053 Wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200469

http://www.thecodecage.com/forumz

Try this Worksheet_Change event code. I named the range which should have no
duplicate values UniqRng in this example.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range, xx As Range
For Each tgt In Target
If (Not Intersect(tgt, ActiveSheet.Range("UniqRng")) Is Nothing) And _
(Len(tgt.Value) 0) Then
For Each xx In ActiveSheet.Range("UniqRng")
If xx.Address < tgt.Address Then
If xx.Value = tgt.Value Then
xx.Value = vbNullString
End If
End If
Next xx
End If
Next tgt
End Sub


This code should be placed on the code page of the worksheet where you want
this to work.

Hope this helps,

Hutch

"Michael" wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks
.

No VBA required. Suppose it's column B which yuo want to limit to unique
values. Select the whole column, use Data/Validation, select Custom from the
"Allow" dropdown, enter this formula:
=COUNTIF(B:B,B1)=1
and perhaps click the Error Alert tab to supply a message when a duplicate
value is entered.

"Michael" wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would hold
the unique value.

I got something sort of working with the selection change event but it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks
.

Bob Umlas, Excel MVP;716765 Wrote:

No VBA required.


I suspect it might be, Bob. How otherwise would "if a value is entered
that already exists in the range, the value that already exists would
become empty and the new location would hold the unique value" be done
without it?


Bob Umlas, Excel MVP;716765 Wrote:

No VBA required. Suppose it's column B which yuo want to limit to
unique
values. Select the whole column, use Data/Validation, select Custom from
the
"Allow" dropdown, enter this formula:
=COUNTIF(B:B,B1)=1
and perhaps click the Error Alert tab to supply a message when a
duplicate
value is entered.

"Michael" wrote:

I am looking for a way to limit a named range to unique values. So
that if a value is entered that already exists in the range the value
that already exists would become empty and the new location would
hold
the unique value.

I got something sort of working with the selection change event but
it
took too long to cycle through each cell in the range to make sure it
did not equal the target value. was not pretty. The range is only
like 50 cells.

I am looking for advise on how to make it work.

Thanks
.



--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=200469

http://www.thecodecage.com/forumz

On May 4, 5:53*pm, Tom Hutchins
wrote:
Try this Worksheet_Change event code. I named the range which should have no
duplicate values UniqRng in this example.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tgt As Range, xx As Range
For Each tgt In Target
* * If (Not Intersect(tgt, ActiveSheet.Range("UniqRng")) Is Nothing) And _
* * * * (Len(tgt.Value) 0) Then
* * * * For Each xx In ActiveSheet.Range("UniqRng")
* * * * * * If xx.Address < tgt.Address Then
* * * * * * * * If xx.Value = tgt.Value Then
* * * * * * * * * * xx.Value = vbNullString
* * * * * * * * End If
* * * * * * End If
* * * * Next xx
* * End If
Next tgt
End Sub

This code should be placed on the code page of the worksheet where you want
this to work.

Hope this helps,

Hutch

This worked perfectly. Thank you very much.!