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

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" wrote:

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

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


"JLGWhiz" wrote:

> 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" wrote:
>
> > 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
> >

On Apr 5, 9:08*am, T. Jenkins <TJenk...@discussions.microsoft.com>
wrote:
> 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
>
>
>
> "JLGWhiz" wrote:
> > 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" wrote:
>
> > > 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 througheach
> > > row to find the rows I can eliminate, then delete each one. *In thiscase,
> > > 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- Hide quoted text -
>
> - Show quoted text -

Hi Todd,

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

Cheers,
Ivan.