Excel Sheet with a Macro is Getting Slower As the Entries Increase

[excelnow]

Hi,

I have an excel sheet with a macro that checks if my latest entry is already on the column and deletes it automatically if it exists.

There are three columns: website URL, email address and 1-word note.

I have about 1000 rows of data at the moment and it is getting slower as I input more rows. This sheet will keep growing and I guess it will be very difficult to enter new data soon.

Here is the macro I use for duplicate detection and deletion of the last entry if it exists:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Rng As Range, Dn As Range
    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
                Selection.ClearContents
            End If
End Sub

Until about 500-600 entries, it was working really fast, but now, when I enter new data into that column, it takes about 2 seconds.

I deleted the macro and added a conditional formating rule on Column A and it highlights the last entry if it already exists on the column. Well, that's fast, but I need to delete it manually now. This was the beauty of the macro.

Do you have any comments on this? It seems I will have to go with no macro solution (conditional formating).

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
If Target.Count <> 1 Then Exit Sub
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .EnableEvents = False
End With
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
If Application.CountIf(Rng, Target) > 1 Then
    Target.ClearContents
End If
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
    .EnableEvents = True
End With
End Sub