Can anyone speed up my slow code to delete repeated rows?

[killpaddy]

The small section of code below is part of a much larger sub and works fine, however for some reason it takes very long to run (longer than the rest of my code). I have no idea why it is running so slowly, perhaps something to do with the 3 nested IF loops; Is there anyway to run it more efficiently?

The information on the sheet is already sorted so that column V is in ascending order and are numbers (therefore any duplicate will be directly below its counterpart). If the number in column V matches the one below the current row AND EITHER column AD or column AE match the value below the current row, then the duplicate row should be deleted and the rest of the list moved up one row to fill the gap. 'LastRowOfList' only ever has a value of around 50 to 100.

For i = 2 To LastRowOfList
    If Not Worksheets("VS Stock Comparison").Range("AD" & i + 1).Value = vbNullString _
        And Not Worksheets("VS Stock Comparison").Range("AE" & i + 1).Value = vbNullString Then
        If Worksheets("VS Stock Comparison").Range("V" & i) = Worksheets("VS Stock Comparison").Range("V" & i + 1) Then
                       
                Worksheets("VS Stock Comparison").Range("V" & i + 2 & ":AM" & LastRowOfList).Cut _
                Destination:=Worksheets("VS Stock Comparison").Range("V" & i + 1 & ":AM" & LastRowOfList - 1)
                LastRowDeleted = True
            
            ElseIf Worksheets("VS Stock Comparison").Range("AE" & i) = Worksheets("VS Stock Comparison").Range("AE" & i + 1) Then
            
                Worksheets("VS Stock Comparison").Range("V" & i + 2 & ":AM" & LastRowOfList).Cut _
                Destination:=Worksheets("VS Stock Comparison").Range("V" & i + 1 & ":AM" & LastRowOfList - 1)
                LastRowDeleted = True
                
            End If
        
        End If
     
    End If
    
    LastRowOfList = Worksheets("VS Stock Comparison").Range("Z" & Rows.Count).End(xlUp).Row
    If LastRowDeleted = True Then
        i = i - 1
        LastRowDeleted = False
    End If
Next

Any help would be much appriciated

 

[CWatts]

Have you disabled screen updating and calculation in your calling sub?

Sub Stuff()
'Put at the start
Application.ScreenUpdating = False
Application.Calculation = xlCalculationAutomatic

'other code here

'put at the end
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = True
End Sub

If you need to rely on cells calculating, you can use Application.Calculate to do a one time calculation.

 

[killpaddy]

sorry, missed a bit of the code out. This is a sub that is called from within another sub:

Sub Remove_Repeats()

LastRowOfList = Worksheets("VS Stock Comparison").Range("Z" & Rows.Count).End(xlUp).Row
LastRowDeleted = False

For i = 2 To LastRowOfList
    
    If Not Worksheets("VS Stock Comparison").Range("AD" & i + 1).Value = vbNullString _
        And Not Worksheets("VS Stock Comparison").Range("AE" & i + 1).Value = vbNullString Then

        If Worksheets("VS Stock Comparison").Range("V" & i) = Worksheets("VS Stock Comparison").Range("V" & i + 1) Then
        '
            If Worksheets("VS Stock Comparison").Range("AD" & i) = Worksheets("VS Stock Comparison").Range("AD" & i + 1) Then
            
               
                
                Worksheets("VS Stock Comparison").Range("V" & i + 2 & ":AM" & LastRowOfList).Cut _
                Destination:=Worksheets("VS Stock Comparison").Range("V" & i + 1 & ":AM" & LastRowOfList - 1)
                LastRowDeleted = True
            

            ElseIf Worksheets("VS Stock Comparison").Range("AE" & i) = Worksheets("VS Stock Comparison").Range("AE" & i + 1) Then

                Worksheets("VS Stock Comparison").Range("V" & i + 2 & ":AM" & LastRowOfList).Cut _
                Destination:=Worksheets("VS Stock Comparison").Range("V" & i + 1 & ":AM" & LastRowOfList - 1)

                LastRowDeleted = True
                
            End If
        
        End If
     
    End If
    
    LastRowOfList = Worksheets("VS Stock Comparison").Range("Z" & Rows.Count).End(xlUp).Row

    If LastRowDeleted = True Then
        i = i - 1
        LastRowDeleted = False
    End If

Next
End Sub

 

[killpaddy]

CWatts, Ive included that code in the other sub that calls this one, so that should still work right?

Also are xlCalculationManual and xlCalculationAutomatic meant to be the other way round?

 

[CWatts]

Also are xlCalculationManual and xlCalculationAutomatic meant to be the other way round?

Ug yes, thanks for catching that. I'd edit but it's too late. That's about the extent of my contributions then.

 

[killpaddy]

Yeah, Ive tried most little trick like that found on the internet, but just this section seems to be sticking. I think, after running it through step by step, it slows the most when executing the cut and paste.