applied a worksheet change event code to find duplicate value with mobile number and then remove duplicate data in entire row & it's working perfectly here is a problem with i have getting run time error after duplicate data has been deleted on entire row
below i have given 3 screen shots
This is the code i have applied
below i have given 3 screen shots
This is the code i have applied
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrList As Variant, cell As Range
Dim rowLast As Long, searchRow As Long
For Each cell In Target
If cell.Column = 4 And Trim(cell.Value2) <> vbNullString Then
rowLast = cell.Parent.Cells(cell.Parent.Rows.Count, 4).End(xlUp).Row
arrList = cell.Parent.Range("D1:D" & rowLast).Value2
For searchRow = LBound(arrList) To UBound(arrList)
If searchRow <> cell.Row Then
If arrList(UBound(arrList), 1) = arrList(searchRow, 1) Then
cell.Parent.Activate
Union(cell, cell.Parent.Range("A" & searchRow & ":D" & searchRow)).Select
MsgBox "This Name and Mobile Number Already Exist in Row No- " & searchRow & _
chr(10) & chr(10) & chr(10) & "This is Duplicate Entry You Have Done Will be Now Removed..."
Application.EnableEvents = False
cell.EntireRow.Delete
Application.EnableEvents = True
End If
End If
Next searchRow
End If
Next cell
End Sub