Notify user of duplicate rows before deleting

[keith05281967]

Greetings VBA Guru's,

I recorded a macro to select column A, find and delete any duplicates found. Here is the code:

Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$37").RemoveDuplicates Columns:=1, Header:=xlYes

I need the code to tell the user if duplicate rows were found or not. If found, give the user the option to proceed with deleting or not. I'm sure this is accomplished w/ an If stmt, but I don't know how to word it.

The above code was just a first pass. The most important thing is that it "notify" the user of duplicates before taking any action.


thanks so much. I'm using excel 07.
Also can you please use code examples with explanations?

thanks in advance for your help,
Keith
<!-- / message -->

 

[Michael M]

Hi Keith
Try

Sub remdupes()
Dim ans As String
ans = MsgBox("You are about to remove duplicate values, Do you wish to proceed ??", vbYesNo)
    If ans = 7 Then
        Exit Sub
    End If
ActiveSheet.Range("$A$1:$A$37").RemoveDuplicates Columns:=1, Header:=xlYes
End Sub

 

[Nalani]

Hi, and welcome to the Board.

If you want this to work "whenever anything" is typed in Column A of the sheet.

Maybe this placed in the Worksheet Code Window. Right Click the Sheet tab > View Code and place there.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim Ans As Integer
    If Target.Count <> 1 Then Exit Sub
    Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    If Application.CountIf(Rng, Target) > 1 Then
        Ans = MsgBox("The Value you entered  " & "(" & Target & ")" & "  is a Duplicate in Column A" _
                     & vbCr & "Do you want to alow it?", vbYesNo)
        If Ans = vbNo Then
            ActiveCell.Offset(-1).Clear
        Else
            Exit Sub
        End If
    End If
End Sub