Macro sub for preventing of entering duplicates in column M in excel

[bajramo]

Hi, I need to write a macro sub for preventing of entering duplicates in column M in excel.

Ex:

I enter "A - 100" in cell M5
If I enter "A - 100" in cell M7 I want to show me an error message that is duplicate.

I tried Data validation but the formula does not work, so I want to use a macro.

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

I think Data Validation should work. try this:

Select the whole column.
Select Data-->Data Tools-->Data Validation.
Set "Allow" to Custom
Set the formula to: =COUNTIF(M:M,M1)<=1
Click OK

That will approve all values where there is no more than one entry in the column.

 

[hiker95]

bajramo,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


Here is a Worksheet_Change event for you to consider.

Sample raw data:

Excel 2007
	M
1	Title M
2
3
4
5	A - 100
6
7
8
Sheet1

If I attempt to enter A - 100 in cell M7, you will get a MessageBox that will display:

Your entry 'A - 100' is a duplicate - duplicate to be deleted!

And, then when you click on the OK button, the duplicate value in cell M7 will be deleted.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/06/2016, ME926175
Dim n As Long
If Intersect(Target, Range("M2:M10000")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim nr As Long
With Application
  .EnableEvents = False
  n = Application.CountIf(Columns(13), Target.Value)
  If n > 1 Then
    MsgBox ("Your entry '" & Target.Value & "' is a duplicate - duplicate to be deleted!")
    Target.Value = ""
  End If
  .EnableEvents = True
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then try entering a duplicate value in Range("M2:M10000").