Results 1 to 5 of 5

Thread: remove duplicates

  1. #1
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    41
    Articles
    0
    Excel Version
    Office 365

    remove duplicates



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    267
    Articles
    0
    Excel Version
    2019
    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

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    824
    Articles
    0
    Excel Version
    Excel 2010
    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:
    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

  4. #4
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    41
    Articles
    0
    Excel Version
    Office 365
    Thank you.

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

    Thanks, again.

  5. #5
    Acolyte r121a947's Avatar
    Join Date
    Jun 2019
    Posts
    41
    Articles
    0
    Excel Version
    Office 365
    Thanks.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •