Bdtyktl

I'm new to VBA so the code I've written is very sloppy. My apologies.

The essence of my problem is that I need to delete duplicate rows from a worksheet with approx. 45,000 rows. Each iteration where the row is deleted takes approximately 30 seconds to complete, and I need to do this for thousands of rows. Any suggestions for how to improve my code so that this all goes faster?

Sub delete_duplicate_rows()

For i = 1 To 85000

    If ActiveCell <> ActiveCell.Offset(1, 0) Or ActiveCell.Offset(0, -1) <> ActiveCell.Offset(1, -1) Or ActiveCell.Offset(0, 1) <> ActiveCell.Offset(1, 1) Or ActiveCell.Offset(0, 2) <> ActiveCell.Offset(1, 2) Or ActiveCell.Offset(0, 3) <> ActiveCell.Offset(1, 3) Or ActiveCell.Offset(0, 4) <> ActiveCell.Offset(1, 4) Or ActiveCell.Offset(0, 5) <> ActiveCell.Offset(1, 5) Or ActiveCell.Offset(0, 6) <> ActiveCell.Offset(1, 6) Or ActiveCell.Offset(0, 7) <> ActiveCell.Offset(1, 7) Or ActiveCell.Offset(0, 8) <> ActiveCell.Offset(1, 8) Or ActiveCell.Offset(0, 9) <> ActiveCell.Offset(1, 9) Or ActiveCell.Offset(0, 10) <> ActiveCell.Offset(1, 10) Or ActiveCell.Offset(0, 11) <> ActiveCell.Offset(1, 11) Or ActiveCell.Offset(0, 12) <> ActiveCell.Offset(1, 12) Or ActiveCell.Offset(0, 13) <> ActiveCell.Offset(1, 13) Then

        ActiveCell.Offset(1, 0).Select

        GoTo NextIteration

    Else

    End If

    If ActiveCell.Value = "" Then Exit Sub

    ActiveCell.Offset(0, -1).Range("A1:Q1").Select

    Selection.Delete Shift:=xlUp

    ActiveCell.Offset(0, 1).Range("A1").Select

NextIteration:

    Next i

End Sub

Many thanks.

As others said, your code is slow do to a couple of things like using select, activate and goto

I believe the culprit of the slow performance is the multiple condition check at once, as you could check for changes on cells.

sub delete_duplicate_rows(byref ws as worksheet)



    ' Dim tbl_width as long, tbl_height as long

    ' Dim row_n, col_n as long

    '

    ' This would be the correct form of one line var declarations, as it was stated in the comments

    ' I prefer this style, so I can group things like iters, table sizes, variants for `For each` 

    ' and save some lines and see more code(I've a small screen)



    dim tbl_width as long 

    dim tbl_height as long

    dim row_n as long 

    dim col_n as long

    with ws

        for row_n=tbl_height to 2 step -1

            for col_n=1 to tbl_width

                if .range(.cells(row_n, col_n)) <> .range(.cells(row_n - 1, col_n)) then

                    .range(.cells(row_n , 1), .cells(row_n, tbl_width)).delete shift:=xlShiftUp

                    col_n = tbl_width

            next col_n

        next row_n

    end with



end sub

This doesn't deppends on a fixed set of rows, don't activate or select anything, and the inner loop could be exited before when one of the conditions is not met. This way you will only check enough to make a decision on wheter to delete.

You could nest another loop, so it will check al the rows not just the one below to it.