Excel 2007 Advanced Filter slowness after 1 record added

[jdop]

I have an Advanced Filter I call repeatedly using different search criteria , where a database is searched, and the resulting records are extracted to another sheet in the same Workbook. Works fine, and is usually lightning fast. On the order of .1 second for 12 searches.

Seem as soon as I add a record to the existing sheet , the Advanced Filter becomes extremely slow and pins the cpu at full usage. The filter still works, but takes 100 times as long!

Something is not right, and I can't figure it out.

This is the basic code that does all the work

Please Login or Register  to view this content.

I have

Please Login or Register  to view this content.

As you can see , the search range is very large but there are only 20,000 populated rows, so adding 1 row to that should not be causing such a slowdown.

Reducing the 999,999 to something smaller speeds up the filter , but its still very slow. It's as if Excel does an optimized search until the range is touched, then forgets how for the remainder of the session. Reloading the sheet fixes it until the next added record.

Any ideas on how to fix this?

Excel 2007
Windows 7 X64, 8 gig ram

[alansidman]

Code Tags Added
Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(Because you are new to the forum, I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[Norie]

Why are you using such a large range if there are only 20000 rows?

Have you tried limiting the range to filter to the actual data?

[jdop]

Yes, I could and should make the range dynamic based on the last used cell.
This is a very old project, and doing it the current way has not been a problem until fairly recently.

I don't think it would address the slowdown problem in any case based on my recent testing. It would be less of an issue with a smaller range, granted. But over time , it would still be a big problem as the data grows.

What possible excuse is there for Excel to take near ZERO time one pass, and then 5-8 seconds on the next to do the exact same operation?

[jdop]

Tried reducing the range and as expected it does not help at at all.

Can reliably get this to break, just add a single row to the data, not inserting, just new data copied into the next row.

Time Before, and after, in seconds. Pretty sad.

time= 0.1796875
time= 35.69141


Is this some unheard of problem?

fwiw, just copied that data to another sheet in the workbook. There are other things going on in the original sheet, though they should not have any impact on this issue.

After a few tests I dont see the problem when using the new sheet as source.

This is a project I've been working on for a decade, and have solved many issues with excel weirdness.

[Norie]

Could you post your code?

Also, what 'other' things are happening in the worksheet?

[jdop]

Well, other than the single criteria, a persons name, there's nothing in code other than what I already posted.

That's whats so strange, very little room for Excel to get confused.

There are some vlookups on the sheet that access the same range, but as I've said, nothing is negatively affected until some trivial text is added to the sheet, which makes no sense at all.

I'll have to assume theres something odd in the sheet structure that cant be divined and work around it.

[Norie]

No other code?

[jdop]

lol, the hangup is right at that piece of code I posted. There are many lines of code, none relevant to this.

AdvancedFilter is where the slowdown is. It runs, but at 1/100th normal speed.

So far , moving that data to its own sheet, seems to have solved the problem in limited testing.

I say, its some nasty memory management issue internal to Excel.

The problem may not even show up in later versions.