Is There the Potential for a "Faster Code"?

deep6

Board Regular
Joined
Apr 20, 2004
Messages
55
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.
Code:
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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
 

deep6

Board Regular
Joined
Apr 20, 2004
Messages
55
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

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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.

deep6 said:
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.
Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,901
Members
431,772
Latest member
dannyboi1

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