Results 1 to 3 of 3

Thread: duplicate record move to another sheet name Duplicate

  1. #1
    Seeker aswathy0001's Avatar
    Join Date
    Nov 2016
    Location
    india
    Posts
    13
    Articles
    0
    Excel Version
    2010

    Post duplicate record move to another sheet name Duplicate



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

    i am using this code for move the entire row if column H had the value "Duplicate" i had more than 10000 total records but very few had "Duplicate" in H column. so its take time because each record in if loop. only few records having the value duplicate in H column. is that any other solution to make it fast?

    Code:
    Sub MoveDuplicate2()Application.ScreenUpdating = False
    
    
    Dim i As Integer
    Dim acount
    acount = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To acount
    If Range("H" & i).Value = "Duplicate" Then
    Range("A" & i).Select
    ActiveCell.EntireRow.Cut
     Sheets("Duplicate").Select
     Sheets("Duplicate").Range("A65536").End(xlUp).Offset(1, 0).Select
     ActiveSheet.Paste
     Sheets("Complete_List").Select
    ActiveCell.EntireRow.Delete Shift:=xlUp
    i = i - 1
    End If
    Next i
    i = i + 1
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    
    
    End Sub

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    782
    Articles
    0
    Excel Version
    Excel 2010
    Have you tried with filtering, something along the lines of
    Code:
    Sub MoveDuplicate_3()
    Dim filtRng As Range
    Set filtRng = Sheets("Complete_List").UsedRange
    With filtRng
        .AutoFilter Field:=8, Criteria1:="Duplicate", Operator:=xlFilterValues
        .Offset(1).Copy Sheets("Duplicate").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
    End Sub

  3. #3
    Seeker aswathy0001's Avatar
    Join Date
    Nov 2016
    Location
    india
    Posts
    13
    Articles
    0
    Excel Version
    2010
    perfect
    thanks dear

Posting Permissions

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