Deleting Rows in Large Table

[T. Jenkins]

I'm working with a large data set in Excel, over 38,000 rows. I'm trying to
combine two such sets, then do some pivots, but because the combined table is
too large, I need to eliminate some unecessary data.

My approach so far has been to pull in the first set, then run through each
row to find the rows I can eliminate, then delete each one. In this case,
I'm trying to delete any row with "2007" as the year. Everything seems to be
working, but once the code finds the 2007 rows, it's taking a very long time
to run through the data. I've already turned off screen refresh, and set
calculation mode to manual, but it's still taking a very long time.

When I run though the code step by step, I don't detect any pauses or
problems deleting the rows. It seems to run thorugh process very quickly.

Is there any reason why this should take a long time? Is there a better
approach for doing this?

Thanks,
Todd

 

[JLGWhiz]

You might get better results if you post the code you are currently using to
do the deletions. Otherwise, there is nothing to compare to for determining
if there is a better way.

 

[T. Jenkins]

I may have come up with a solution. I was originally cycling through every
rough, checking for the cell entry, and deleting if it matched my criteria.
I'm now trying to use Autofilter to just select those rows that match my
criteria.

If this doesn't work, I'll reply with a copy of the code.

Thanks,
Todd

 

[Ivyleaf]

I may have come up with a solution.  I was originally cycling through every
rough, checking for the cell entry, and deleting if it matched my criteria..  
I'm now trying to use Autofilter to just select those rows that match my
criteria.

If this doesn't work, I'll reply with a copy of the code.

Thanks,
Todd






- Show quoted text -

Hi Todd,

You'd probably want to use AdvancedFilter rather than AutoFilter.

Cheers,
Ivan.