+ Reply to Thread
Results 1 to 11 of 11

Remove Duplicates Without shifting cells VBA

  1. #1
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Remove Duplicates Without shifting cells VBA

    I'm trying to remove duplicates using VBA macro. Columns a,b,c and d on my sheet all have duplicate text that needs to be removed without shifting cells. After searching I was able to find the VBA macro below but I need to select a range every time. I need to streamline it to only be done once. Can someone please help? Thanks

    Remove.png



    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by MiglA06; 06-29-2020 at 03:49 AM.

  2. #2
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Duplicates Without shifting cells VBA

    1)
    Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    2) untested
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Remove Duplicates Without shifting cells VBA

    Hi there,

    Your macro is deleting the duplicate in the first column selected, the rest of the row remains as is. if this is what you need and you only want to replace the input box try the below.
    Provided your data starts in A1 and has no blank rows in column A, you can do it with changing one line:

    Please Login or Register  to view this content.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  4. #4
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Remove Duplicates Without shifting cells VBA

    Thanks. I've made the changes and attached a test file.

  5. #5
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Remove Duplicates Without shifting cells VBA

    No i want to delete duplicates on multiple columns not just one. I want to delete duplicates on columns A thru D.

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Duplicates Without shifting cells VBA

    Do you mean?
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Remove Duplicates Without shifting cells VBA

    Yes that worked good but it also erased text from column E and then gave me a debug error. I only need columns A thru D

    Remove.png

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Duplicates Without shifting cells VBA

    OOps, 5 shohld be 4
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Remove Duplicates Without shifting cells VBA

    That worked perfect but it still gave me a debug error on the this.

    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Remove Duplicates Without shifting cells VBA

    Do you have Error value?
    try change that line to
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-27-2019
    Location
    Los Angeles,CA
    MS-Off Ver
    Office 2010
    Posts
    21

    Re: Remove Duplicates Without shifting cells VBA

    Yes that did. That works perfectly. Thanks you're awesome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code to remove duplicates without shifting cells
    By belpal in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-03-2018, 08:59 PM
  2. Excel 2003 vba Add and remove from listbox shifting cells up when removed
    By Ditch1983 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2017, 11:26 PM
  3. [SOLVED] Combine cells and remove duplicates
    By deanblew in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2016, 10:09 AM
  4. Remove cells if duplicates found
    By garnerseo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-18-2013, 08:54 AM
  5. [SOLVED] Find Duplicates and add columns while shifting the cells up
    By jed38 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-19-2013, 09:07 AM
  6. [SOLVED] concatinate cells and remove duplicates
    By wellsw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-11-2013, 07:54 PM
  7. Formatting cells to remove duplicates
    By benmcfall in forum Excel General
    Replies: 0
    Last Post: 09-18-2009, 09:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1