im using vba coding for my excel sheet application.
where the user enters all the data in the excel sheet and by clicking on validate button a text file will be generated ,and if the user enters any wrong data say text in where numeric fields are only allowed it shows an alert and text file wnt be generated.
now i require some help if the user enters duplicate values ie combination of columns A,D,E are unique if any duplicate row is found withsame values for A,D,E
alert should be shown ie
col A col B colC col D col E col F
1 2 3 4 5 6
1 3 4 4 5 7
1 2 3 4 6 6
the output should be
col A col B colC col D col E col F
1 2 3 4 5 6
1 2 3 4 6 6
my code is:
Public Function DeleteDuplicaterecords(Opt As String) As Boolean
Dim x As Long
Dim LastRow As Long
If Opt = "P" Then
sheet1.Select
Else
sheet2.Select
End If
LastRow = Range("A65536").End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 And Application.WorksheetFunction.CountIf(Range("D1:D" & x), Range("D" & x).Text) > 1 And Application.WorksheetFunction.CountIf(Range("E1:E" & x), Range("E" & x).Text) > 1 Then
MsgBox "Duplicate Record is found,combination for Tin no,Invoice No and Invoice Date are Unique"
Range("A" & x).Select
DeleteDuplicaterecords = False
Exit Function
End If
Next x
DeleteDuplicaterecords = True
End Function
Bookmarks