Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 __ |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 __ |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __ |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 __ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Tables filtering data | Excel Discussion (Misc queries) | |||
Filtering Columns to Align Matching Data | Excel Worksheet Functions | |||
filtering data | New Users to Excel | |||
Saving data in a worksheet within a workbook | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |