Slow deletion of filtered region

[jomili]

Below I've posted a snippet of code that's giving me trouble. The snippet comes at the end of a long procedure of configuring a pivot, converting it to values, and populating it with a formula that results in either "Delete" or "Keep", then sorting by "Delete" and "Keep", then filtering to "Delete". All of that goes quickly. Then we try to Delete the "Delete" lines. That little step takes approximately 15 minutes, and I can't figure out why. Screenupdating, calculations, alerts are all turned off, and there's no error message. the code completes, it takes forever to do the deletion. There ARE 352K lines, so I could understand a small delay, but that long means to me that something's wrong. Any ideas?

Please Login or Register  to view this content.

[rorya]

I suspect you'd find it quicker to use:

Please Login or Register  to view this content.

[jomili]

I'll try that as soon as my macro finishes. Is it because, as it's written now, the deletion is cell by cell, rather than entire rows at a time?

[rorya]

I imagine that is the main cause, yes.

[jomili]

Well, it was a good idea, but it doesn't seem to be bearing fruit. it's been running about 10 minutes so far, and I'm about to leave work for the day, so I'll try to get back to it tomorrow.

I'm wondering; would it make it any better to select the range, then do a "selection.entirerow.delete"?

[rorya]

It shouldn't. Is it a regular range or a table?

Also, you did change the range to just one column?

[jomili]

it's just a regular range. here's the entire macro, in case you can spot something else I'm doing wrong. But by putting STOP points, I can see that all steps proceed quickly, until we come to deletion.
That takes forever.

Please Login or Register  to view this content.

[rorya]

If you stop the code at that point and delete the rows manually, how long does it take?

[jomili]

Now THAT was a good question to ask! I just tried it; the manual deletion has so far taken 15 minutes, and I'm about to kill it. Could it be because the Workbook itself is huge, with multiple pivots all based on an external source? Would it be better to copy my data to a new WB and do the deletion there?

[rorya]

It's worth a shot. Off the top of my head I can't see why it should take that long on manual calculation.

[jomili]

So I changed my macro around; I filter out some stuff in the pivot now, which reduces the lines from 352K to about 250K. I then convert my pivot to values, move that sheet into a new workbook, close the old source workbook, THEN delete the lines. everything before the deletion takes about a minute. The Deletion is still taking almost 15 minutes. I give up!