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

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
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.

Code:
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

CWatts

Well-known Member
Joined
Jan 22, 2010
Messages
701
Have you disabled screen updating and calculation in your calling sub?
Code:
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

Board Regular
Joined
Jul 31, 2012
Messages
52
sorry, missed a bit of the code out. This is a sub that is called from within another sub:

Code:
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

Board Regular
Joined
Jul 31, 2012
Messages
52
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

Well-known Member
Joined
Jan 22, 2010
Messages
701
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

Board Regular
Joined
Jul 31, 2012
Messages
52
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,140
Messages
5,622,949
Members
415,942
Latest member
Data Midwife

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top