remove duplicates

[r121a947]

I am trying to make a RemoveDuplicates macro. The problem I have is that once a Remove Duplicates routine is recorded, the macro remembers the original used range and does not always cover the entire used range.

Is there an easy way to return/retrieve the last row's number? Or the last cell's address? That would make the macro always check/use the entire used range.

My recorded macro code is below.

Also, is there a way to stop the macro recorder before the Remove Duplicates process is started when one clicks OK?

All help is greatly appreciated.

Thanks.

Sub RemoveDuplicates()
'
' RemoveDuplicates Macro
' Remove dupes on B, C, D
'
' Keyboard Shortcut: Ctrl+Shift+X
'
    ActiveSheet.Range("$A$1:$L$1045").RemoveDuplicates Columns:=Array(2, 3, 4), _
        Header:=xlNo
End Sub

[alansidman]

This identifies the last row in column A

Option Explicit

Sub RemoveDuplicates()
'
' RemoveDuplicates Macro
' Remove dupes on B, C, D
'
' Keyboard Shortcut: Ctrl+Shift+X
'
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:L" & lr).RemoveDuplicates Columns:=Array(2, 3, 4), _
        Header:=xlNo
End Sub

[NoS]

Also, is there a way to stop the macro recorder before the Remove Duplicates process is started when one clicks OK?

Maybe the addition of a message box will look after this for you.
Adding to Alan's suggestion:

Sub RemoveDuplicates()
'
' RemoveDuplicates Macro
' Remove dupes on B, C, D
'
' Keyboard Shortcut: Ctrl+Shift+X
'
    Dim lr As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to do this ?" & vbLf & _
                    "Click Yes to continue", vbYesNoCancel, _
                    "This is your chance to change your mind")
    If answer <> vbYes Then Exit Sub
    
    lr = Range("A" & Rows.Count).End(xlUp).Row
    ActiveSheet.Range("$A$1:L" & lr).RemoveDuplicates Columns:=Array(2, 3, 4), _
        Header:=xlNo
End Sub

[r121a947]

Thank you.

Looks perfect . . . I never know the right syntax for these things.

Thanks, again.

[r121a947]

Thanks.

Your solution seems to end the process . . . I want to pause the process, but then have it completed after I click OK . . .