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