Slow data filtering
 

Hi,

I'm hoping someone can help me out.

I've been running a data filter macro

Range("hist_data").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("job_crit"), _
copytorange:=Range("hist_out"), unique:=False

sucessfully for a couple of years. By successfully I mean that the
line above used to be actioned instantaneously as I F8 through it.

However in attempting to deduce why the macro that calls this code
(which loops about 400 times) was taking so long, I now find that
these two lines of code are taking about 6 seconds.

I originally thought that the database being filtered, now at about
30,000 rows, might be contributing to the problem. But I've taken a
copy of the database to a new blank workbook, closed the original
workbook and created the appropriate range names in the new workbook
and it works as normal, i.e. instantaneously. When I re-open the first
workbook however the problem of slow running has returned.

Has anyone any ideas for curing this? This is beginning to cause me
serious time problems.

The first workbook consist of about 20 worksheets and is about 30 MB
in size, and contains about 250 defined range names.

I'm using Windows 2000 and Excel 2002 SP3 if that helps in any way.

Usual TIA

Regards




Richard Buttrey
__

Slow data filtering
 

I've noticed slow downs after I do a file|print or File|PrintPreview. Those
dotted lines that show up that indicate where the new page breaks can slow down
excel.

If you're adding/deleting rows/columns, then excel figures that it should
determine where to redraw those lines.

I'm not sure if that's a good fit for the .advancedfilter stuff--but I would try
turning those dotted lines off.

I've also noticed that things can slow down if I'm in view|page break preview
mode.

You may want to try something like:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

to see if it helps.

And it's never a bad idea to clean up the windows temp folder--it might not help
this cause, but it may help other problems (not quite noticeable yet).

close excel (and any other running applications)
windows start button|Run
%temp%

Clean as much as you can.
Richard Buttrey wrote:

Hi,

I'm hoping someone can help me out.

I've been running a data filter macro

Range("hist_data").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("job_crit"), _
copytorange:=Range("hist_out"), unique:=False

sucessfully for a couple of years. By successfully I mean that the
line above used to be actioned instantaneously as I F8 through it.

However in attempting to deduce why the macro that calls this code
(which loops about 400 times) was taking so long, I now find that
these two lines of code are taking about 6 seconds.

I originally thought that the database being filtered, now at about
30,000 rows, might be contributing to the problem. But I've taken a
copy of the database to a new blank workbook, closed the original
workbook and created the appropriate range names in the new workbook
and it works as normal, i.e. instantaneously. When I re-open the first
workbook however the problem of slow running has returned.

Has anyone any ideas for curing this? This is beginning to cause me
serious time problems.

The first workbook consist of about 20 worksheets and is about 30 MB
in size, and contains about 250 defined range names.

I'm using Windows 2000 and Excel 2002 SP3 if that helps in any way.

Usual TIA

Regards

Richard Buttrey
__

--

Dave Peterson

Slow data filtering
 

On Sun, 29 Jan 2006 11:01:27 -0600, Dave Peterson
wrote:

I've noticed slow downs after I do a file|print or File|PrintPreview. Those
dotted lines that show up that indicate where the new page breaks can slow down
excel.

If you're adding/deleting rows/columns, then excel figures that it should
determine where to redraw those lines.

I'm not sure if that's a good fit for the .advancedfilter stuff--but I would try
turning those dotted lines off.

__


Dave,

Thanks for the prompt response - and on a Sunday as well :-).

I don't think it was anything to do with your suggestions, but I've
filed them away for future ref. You never know.

I think I've finally tracked it down - or at least I've managed to get
it back to normal but don't really understand why.
I selectively deleted other sheets in the workbook, testing the filter
macro each time, and found that after deleting one of them, the
filtering macro worked as normal. What seems strange is that the sheet
I deleted was neither the sheet containing the data being filtered,
(obviously !) or the sheet the data was being copied to. The only
remote connection I can see is that on the sheet that received the
filtered data, (but not the area to which the data is copied), there
are formulae which refer to the deleted sheet.

The deleted sheet receives its data via an upload from another
application, so I went back to basics and reloaded that data again.

That seems to have cured the problem, but I'm really non the wiser as
to what the fundamental problem might have been.

All part of life's rich pageant as they say.


Rgds, and thanks again.
Richard Buttrey
__

Slow data filtering
 

Glad you found the solution and glad you posted it. Now google can help the
next person.

Richard Buttrey wrote:

On Sun, 29 Jan 2006 11:01:27 -0600, Dave Peterson
wrote:

I've noticed slow downs after I do a file|print or File|PrintPreview. Those
dotted lines that show up that indicate where the new page breaks can slow down
excel.

If you're adding/deleting rows/columns, then excel figures that it should
determine where to redraw those lines.

I'm not sure if that's a good fit for the .advancedfilter stuff--but I would try
turning those dotted lines off.

__

Dave,

Thanks for the prompt response - and on a Sunday as well :-).

I don't think it was anything to do with your suggestions, but I've
filed them away for future ref. You never know.

I think I've finally tracked it down - or at least I've managed to get
it back to normal but don't really understand why.
I selectively deleted other sheets in the workbook, testing the filter
macro each time, and found that after deleting one of them, the
filtering macro worked as normal. What seems strange is that the sheet
I deleted was neither the sheet containing the data being filtered,
(obviously !) or the sheet the data was being copied to. The only
remote connection I can see is that on the sheet that received the
filtered data, (but not the area to which the data is copied), there
are formulae which refer to the deleted sheet.

The deleted sheet receives its data via an upload from another
application, so I went back to basics and reloaded that data again.

That seems to have cured the problem, but I'm really non the wiser as
to what the fundamental problem might have been.

All part of life's rich pageant as they say.

Rgds, and thanks again.
Richard Buttrey
__

--

Dave Peterson

Slow data filtering
 

Richard,

I am glad you solved your issue.........In reading your post, it appears you
were having trouble with something similar to what I am trying to do. In
this same forum, I have posted a question titled "Filtered List to New
Worksheet". Would you review that question and let me know if it is similar
to what you are succesfully doing in your workbook.

Thanks
Rudy

"Richard Buttrey" wrote:

Hi,

I'm hoping someone can help me out.

I've been running a data filter macro

Range("hist_data").AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=Range("job_crit"), _
copytorange:=Range("hist_out"), unique:=False

sucessfully for a couple of years. By successfully I mean that the
line above used to be actioned instantaneously as I F8 through it.

However in attempting to deduce why the macro that calls this code
(which loops about 400 times) was taking so long, I now find that
these two lines of code are taking about 6 seconds.

I originally thought that the database being filtered, now at about
30,000 rows, might be contributing to the problem. But I've taken a
copy of the database to a new blank workbook, closed the original
workbook and created the appropriate range names in the new workbook
and it works as normal, i.e. instantaneously. When I re-open the first
workbook however the problem of slow running has returned.

Has anyone any ideas for curing this? This is beginning to cause me
serious time problems.

The first workbook consist of about 20 worksheets and is about 30 MB
in size, and contains about 250 defined range names.

I'm using Windows 2000 and Excel 2002 SP3 if that helps in any way.

Usual TIA

Regards




Richard Buttrey
__

Slow data filtering
 

On Sun, 29 Jan 2006 18:03:18 -0800, rudawg
wrote:

Richard,

I am glad you solved your issue.........In reading your post, it appears you
were having trouble with something similar to what I am trying to do. In
this same forum, I have posted a question titled "Filtered List to New
Worksheet". Would you review that question and let me know if it is similar
to what you are succesfully doing in your workbook.

Thanks
Rudy

Although I'm doing a lot more than your task, the bit about filtering
is exactly the same. The only difference being that I'm running a
filter with a macro looping about 400 times to get 400 filtered sets
of data, whereas you're doing it manually. My problem was that I'd
seen a big increase in time which I couldn't understand.

However I've posted a response over in the other thread.

Hope it helps,

Rgds


Richard Buttrey
__