Need to delete Duplicate active cell in ROW (ROOM or Subject)
Hello,
Thanks lot your Interest about my Problem and also appreciate your support.
I have excel sheet I need to prevent duplicate entry on SUB or ROOM in one ROW.
As example after I enter RM# as 1 in E4, This need to stop pick the room 1 in entire Row, also I link through data Validation .
if Some one pick the RM# 1 in Cell Q1, That Cell need to delete by popping up Error of " Entering Duplicate value not allowed"
also need to do same for Subject ("SUB" )as well.
Best regard
Indika
For any of these, use the custom formula option of Data Validation, with a formula like this (select all the cells with D4 as the active cell)
=COUNTIF(4:4,D4)=1
Change the error/entry messages to suit.
Bernie Deitrick
Excel MVP 2000-2010
Hello,
Thanks lot for your respond, Yes I found the same solution earlier as well.
I need to keep my data validation for select the subject and Room, But if I use this formula for data validation I can not do that.
So I need to keep my data validation as its and Duplicate control need to do using VBA.
Thanks Again
Best regard
Indika
1) Copy this code.
2) Right-Click the sheet tab of interest.
3) Select "View Code"
4) Paste the code into the window that appears.
5) Save the file as a macro-enabled .xlsm file.
6) Make changes as needed
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Application.CountIf(Target.EntireRow, Target.Value) > 1 Then Application.EnableEvents = False Application.Undo MsgBox "Don't duplicate values in a row!" Application.EnableEvents = True End If End Sub
Dear Bernie,
Thank lot for the all support because it is working well.
if Possible Please help to get this same result only for E, G, I , K, M, O, Q and S (Which mention only Room # - RM# in Row 3)
Best regard
Indika
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Intersect(Target, Range("E:E, G:G, I:I , K:K, M:M, O:O, Q:Q, S:S")) Is Nothing Then Exit Sub If Application.CountIf(Target.EntireRow, Target.Value) > 1 Then Application.EnableEvents = False Application.Undo MsgBox "Don't duplicate values in a row!" Application.EnableEvents = True End If End Sub
Hello Bernie,
I just check it Properly, So it crashing with date and week number, So please help to support to apply for range D:S as one option.
Also as I mention above as 2nd Option need to apply the only for RM# Column.
So sorry for the confusion
Best regard
Indika
Dear Bernie,
So sorry to come back to you again,
Because in My Excel i am having heading till 3rd Row,
So I Try to use a different heading to Overcome to from this But it not help to get final format.
So please Help me to take out the 1st 3 Rows from Equation
Then Final formula need to Skip first 3 Column, and Then need to apply "E:E, G:G, I:I , K:K, M:M, O:O, Q:Q, S:S"
In the mean time if you can help me to select only Specify Rows Range like 4:1000, That also Help.
Best regard
Indika
The final code does skip the first 3 columns - this also skips the first 3 rows:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Target.Value = "" Then Exit Sub If Target.Row < 4 Then Exit Sub 'Ignore rows 1,2, and 3 If Intersect(Target, Range("E:E, G:G, I:I , K:K, M:M, O:O, Q:Q, S:S")) Is Nothing Then Exit Sub If Application.CountIf(Target.EntireRow, Target.Value) > 1 Then Application.EnableEvents = False Application.Undo MsgBox "Don't duplicate values in a row!" Application.EnableEvents = True End If End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks