duplicate record move to another sheet name Duplicate

A

aswathy0001

New member
Joined
Nov 29, 2016
Messages
24
Reaction score
0
Points
0
Location
india
Excel Version(s)
2010
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
 
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
 
You must log in or register to reply here.
Back
Top