amruthubballi
New Member
- Joined
- Apr 27, 2017
- Messages
- 15
I want to restrict entering duplicate data in multiple column.
<tbody>
</tbody>
From the above table we can see that Row no’s 21, 22 & 23 are unique. In Row no’s 21 & 23 are almost same but they differ in form type hence they are also unique. Where as in Row no’s 21 & 24 they are exact same in all column so basically I want a VBA code which should restrict from entering same data twice in Table. If there is a change in one column is acceptable but it cannot be same in all column.
I have got a following code to restrict entering data in only one column
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("B21:e120"), Target) > 1 Then
MsgBox "Dulipate Data!", vbCritical, "Remove Data"
Target.Value = ""
End If
End Sub
Please make necessary adjustment to restrict entering same data in Column B to F
I HOPE SOME ONE WILL HELP ME TO RESOLVE THIS
Thanks in Advance.
B | C | D | F | |
20 | Client Name | Year | Quarter | Form Type |
21 | ABC | 2017-18 | Q1 | 26Q |
22 | XYZ | 2017-18 | Q1 | 26Q |
23 | ABC | 2017-18 | Q1 | 24Q |
24 | ABC | 2017-18 | Q1 | 26Q |
<tbody>
</tbody>
From the above table we can see that Row no’s 21, 22 & 23 are unique. In Row no’s 21 & 23 are almost same but they differ in form type hence they are also unique. Where as in Row no’s 21 & 24 they are exact same in all column so basically I want a VBA code which should restrict from entering same data twice in Table. If there is a change in one column is acceptable but it cannot be same in all column.
I have got a following code to restrict entering data in only one column
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("B21:e120"), Target) > 1 Then
MsgBox "Dulipate Data!", vbCritical, "Remove Data"
Target.Value = ""
End If
End Sub
Please make necessary adjustment to restrict entering same data in Column B to F
I HOPE SOME ONE WILL HELP ME TO RESOLVE THIS
Thanks in Advance.