Preventing Duplicate Entries In Specified Range

Caliz

New Member
Joined
Dec 13, 2016
Messages
2
Hi,

I have tired checking the forums and found close to this, but not quite. I'm trying to do a data validation using: =COUNTIF($B$2:$EZ$980,$B$2:$EZ$980)=1

Trying to prevent duplicate name/number entries from the range of B2:EZ980. I've only been able to get it to validate in cell b2 or all other cells in either row or column only and not the entire area. (E.g. Cell's B2 and N15 should not allow for the same name/number, not just in B3,4,5,6, or C2,D2,E2,F2)

Is this possible?
Thank you very much.
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Maybe try worksheet event:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)


    
    On Error GoTo ErrorHandler
    If Application.WorksheetFunction.CountIf(Range("B2:EZ980"), Range(Target.Address)) > 1 Then
    
        MsgBox "Duplicate!", vbCritical
        
        Target.ClearContents
        
        
        
    End If
ErrorHandler:


End Sub
 
Upvote 0
Thank you. That worked perfect.
I paired this up with the following (Requiring that the macro is enabled before working on the workbook)
Code:
Option Explicit 
 
Const WelcomePage = "Macros" 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False 
     
     'Evaluate if workbook is saved and emulate default propmts
    With ThisWorkbook 
        If Not .Saved Then 
            Select Case MsgBox("Do you want to save the changes you made to '" & .Name & "'?", _ 
                vbYesNoCancel + vbExclamation) 
            Case Is = vbYes 
                 'Call customized save routine
                Call CustomSave 
            Case Is = vbNo 
                 'Do not save
            Case Is = vbCancel 
                 'Set up procedure to cancel close
                Cancel = True 
            End Select 
        End If 
         
         'If Cancel was clicked, turn events back on and cancel close,
         'otherwise close the workbook without saving further changes
        If Not Cancel = True Then 
            .Saved = True 
            Application.EnableEvents = True 
            .Close savechanges:=False 
        Else 
            Application.EnableEvents = True 
        End If 
    End With 
End Sub 
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False 
     
     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI) 
    Cancel = True 
     
     'Turn events back on an set saved property to true
    Application.EnableEvents = True 
    ThisWorkbook.Saved = True 
End Sub 
 
Private Sub Workbook_Open() 
     'Unhide all worksheets
    Application.ScreenUpdating = False 
    Call ShowAllSheets 
    Application.ScreenUpdating = True 
End Sub 
 
Private Sub CustomSave(Optional SaveAs As Boolean) 
    Dim ws As Worksheet, aWs As Worksheet, newFname As String 
     'Turn off screen flashing
    Application.ScreenUpdating = False 
     
     'Record active worksheet
    Set aWs = ActiveSheet 
     
     'Hide all sheets
    Call HideAllSheets 
     
     'Save workbook directly or prompt for saveas filename
    If SaveAs = True Then 
        newFname = Application.GetSaveAsFilename( _ 
        fileFilter:="Excel Files (*.xls), *.xls") 
        If Not newFname = "False" Then ThisWorkbook.SaveAs newFname 
    Else 
        ThisWorkbook.Save 
    End If 
     
     'Restore file to where user was
    Call ShowAllSheets 
    aWs.Activate 
     
     'Restore screen updates
    Application.ScreenUpdating = True 
End Sub 
 
Private Sub HideAllSheets() 
     'Hide all worksheets except the macro welcome page
    Dim ws As Worksheet 
     
    Worksheets(WelcomePage).Visible = xlSheetVisible 
     
    For Each ws In ThisWorkbook.Worksheets 
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden 
    Next ws 
     
    Worksheets(WelcomePage).Activate 
End Sub 
 
Private Sub ShowAllSheets() 
     'Show all worksheets except the macro welcome page
     
    Dim ws As Worksheet 
     
    For Each ws In ThisWorkbook.Worksheets 
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible 
    Next ws 
     
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden 
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top