Is There the Potential for a "Faster Code"?

[deep6]

I'm trying to set up a way to go through a database that I have in Excel and delete rows that have duplicate data. I've based the following code on a sample that I found in this forum, but because the database that I'm going through will often have more than 5,000 rows, this code can take several minutes to fully execute.

I'm wondering if there is another approach to this that will not require the use of "ActiveCell" in the code, and if so, will it provide for a much faster solution?

Essentially, if there is more than one row that has the same combination of values in Columns A and B, then all but one of those rows should be deleted.

ReRun:
Do Until ActiveCell.Offset(1, 0).Text = ""
If ActiveCell.Text = ActiveCell.Offset(1, 0).Text And ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(1, 2).Value Then
ActiveCell.Offset(1, 0).EntireRow.Delete
GoTo ReRun
End If
ActiveCell.Offset(1, 0).Activate
Loop

Thank you for any help that you can provide.

 

[tactps]

Firstly, I would add in:
application.screenupdating = false
at the beginning
and
application.screenupdating = true
at the end.

Also turn your calculation to manual.

See if that helps a bit.

You could also try (untested):

dim cell as range
application.screenupdating = false
range("A1:A"&[a65536].end(xlup).row).select
for each cell in selection
If ActiveCell.Text = ActiveCell.Offset(1, 0).Text And ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(1, 2).Value Then ActiveCell.Offset(1, 0).EntireRow.Delete
next cell
application.screenupdating = true

 

[tactps]

You could also try an advanced filter (see Data menu)

 

[deep6]

Thank you for your response, tactps.

Unfortunately, your suggested options don't seem to affect the speed.

Perhaps as an alternative, if I could at least let the users know how long they will have to wait for the code to run, then they will be less frustrated and less likely to think that their computer has stalled. I'm thinking one way to do this is to use a "progress bar" control on a userform. I suspect that there is some way to determine the number of rows of data that exist prior to any rows being deleted, and then how many rows are still left to be checked once the deletion process begins. If so, then the progress bar could give a visual display of what percentage was complete.

... the problem is, I've never used a progress bar control before, and am not sure where to start. Any ideas?

 

[tactps]

The progress bar can be found be a search of this forum, but it will slow it down even more.

Perhaps the status bar is better:
application.statusbar = "Please wait..."
application.statusbar = "Please keeping waiting..."
application.statusbar = "I'm going as fast as I can and stop using that language..."

At the end:
application.statusbar = false

 

[tusharm]

You should investigate if you can use the advanced filter capabiilty. If you can it will be faster (or at least as fast) as any VBA solution. If you cannot, the following may help speed things up.

First, start from the bottom and work your way up.

Second, don't select or activate anything. It really slows things down. For an example see
Beyond the macro recorder
http://www.tushar-mehta.com/excel/vba/beyond_the_macro_recorder/index.htm

Third, and this is slightly complicated, use the Union method to build a list of rows to delete and then delete them in one step. The reason this gets complicated is that the Union method itself slows down when dealing with a range that contains some number of discontiguous areas. So, I would add about 50 to 60 entries to the range variable that contains the 'rows to be deleted' using the Union method. Then delete those rows, reset various variables and continue with the main loop.

I'm trying to set up a way to go through a database that I have in Excel and delete rows that have duplicate data. I've based the following code on a sample that I found in this forum, but because the database that I'm going through will often have more than 5,000 rows, this code can take several minutes to fully execute.

I'm wondering if there is another approach to this that will not require the use of "ActiveCell" in the code, and if so, will it provide for a much faster solution?

Essentially, if there is more than one row that has the same combination of values in Columns A and B, then all but one of those rows should be deleted.

ReRun:
Do Until ActiveCell.Offset(1, 0).Text = ""
If ActiveCell.Text = ActiveCell.Offset(1, 0).Text And ActiveCell.Offset(0, 2).Value = ActiveCell.Offset(1, 2).Value Then
ActiveCell.Offset(1, 0).EntireRow.Delete
GoTo ReRun
End If
ActiveCell.Offset(1, 0).Activate
Loop

Thank you for any help that you can provide.