remove duplicates

R

r121a947

New member
Joined
Jun 29, 2019
Messages
49
Reaction score
0
Points
0
Excel Version(s)
Office 365
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.

Code: 
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
 
This identifies the last row in column A

Option Explicit


Code: 
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
 
Also, is there a way to stop the macro recorder before the Remove Duplicates process is started when one clicks OK?
Click to expand...
Maybe the addition of a message box will look after this for you.
Adding to Alan's suggestion:
Code: 
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
 
Thank you.

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

Thanks, again.
 
Thanks.

Your solution seems to end the process . . . I want to pause the process, but then have it completed after I click OK . . .
 
You must log in or register to reply here.
Back
Top