+ Reply to Thread
Results 1 to 5 of 5

remove duplicates not working does not rmove all

  1. #1
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    remove duplicates not working does not rmove all

    hi#
    I have been trying to get rid of duplicates in sheets ,seems to work with some sheets and not others,have just noticed with sheet2 autofilter is on and if I enter l90
    c567 is there and if hover mouse over filter says c567 equals=l90,
    to work click commandbutton top centre and it will split vessel ,valve,gearbox into different sheets but gearbox and pump doubles up (duplicates) and others don't
    I am completely lost and help please as to why these 2 things are happening
    cheers colin
    Attached Files Attached Files
    Last edited by cfinch100; 09-05-2015 at 08:36 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: remove duplicates not working does not rmove all

    It would probably help if you posted the code. No plans to wade through a dozen or more modules looking for something that looks like it's going to delete rows.

    Generally, if you are deleting rows based on criteria, you need to start at the bottom and work your way up or entries may be "skipped".

    So, something like:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    Re: remove duplicates not working does not rmove all

    hi
    this is the code that's looks at sheet2 and makes new sheets depending on type ,the macrodupagain is the 10th delete macro ive tried,this is self recorded but does not always work
    cheers colin




    Please Login or Register  to view this content.
    Dim lrow As Long
    Dim i As Long
    Dim sname As String

    Application.ScreenUpdating = False

    With Worksheets("sheet2")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
    ' If .Range("f" & i).Value = "COMPLETED" Then
    sname = .Range("B" & i).Value
    If Not Evaluate("ISREF('" & sname & "'!A1)") Then
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = sname
    .Rows("1:1").Copy Worksheets(sname).Range("A1")
    End If

    .Rows(i & ":" & i).Copy
    Worksheets(sname).Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlFormats
    Worksheets(sname).Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlValues
    Application.CutCopyMode = 0
    Worksheets(sname).Columns.AutoFit


    Next i
    End With
    Macrodupagain
    Macrodupagain Macro
    '

    '
    Sheets("PUMP").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    ' Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=False
    ' Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=False
    ' Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("VESSEL").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("MOTOR").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("GEARBOX").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("RELIEF VALVE").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Sheets("VALVE").Select
    Range("A1:M170").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Please Login or Register  to view this content.
    [/CODE]

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: remove duplicates not working does not rmove all

    Here is a delete dupe macro that works

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    Re: remove duplicates not working does not rmove all

    hi alansidman
    many thanks for the code works well,only one that keeps working aswell ,many thanks
    cheers colin

+ 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. Replies: 13
    Last Post: 10-22-2019, 02:14 AM
  2. Remove duplicates function not removing duplicates
    By Berilium2 in forum Excel General
    Replies: 3
    Last Post: 04-01-2015, 06:55 AM
  3. Remove Duplicates Not Working!
    By walle786 in forum Excel General
    Replies: 8
    Last Post: 03-24-2013, 12:37 PM
  4. Replies: 1
    Last Post: 10-23-2012, 09:12 AM
  5. Replies: 5
    Last Post: 02-28-2012, 02:52 PM
  6. Need VBA code to remove entries if there are duplicates (remove them totally)
    By BrandonFromSingapore in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-13-2012, 12:50 AM
  7. Replies: 2
    Last Post: 03-20-2011, 11:19 AM

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