Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering Problem
The following code executes in about 2 seconds in excel 2003 but takes 2
mintes in excel 2007. Anyone know why? The size of the database is 50,000 row by 52 columns. Public Sub FilterDbase_Click() Dim filtercontrolSheet As Worksheet Dim resultsSheet As Worksheet Set filtercontrolSheet = Sheets("FilterControl") Set resultsSheet = Sheets("Results") Application.ScreenUpdating = False resultsSheet.Activate resultsSheet.Range("A10:IV65536").Select Selection.ClearContents 'Perform Extract Dim SourceRng As Range Dim CritRng As Range Dim CopyToRng As Range filtercontrolSheet.Activate filtercontrolSheet.Range("Criterion1").Select Selection.ClearContents filtercontrolSheet.Range("Criterion2").Select Selection.Copy filtercontrolSheet.Range("A29").Select ActiveSheet.Paste Set SourceRng = Sheets("PlanData").Range("SourcePlan") Set CritRng = Sheets("FilterControl").Range("A28:AV29") Set CopyToRng = Sheets("Results").Range("A10:AQ10") filtercontrolSheet.Range("PlanHeader").Select Selection.Copy resultsSheet.Activate resultsSheet.Range("A10").Select ActiveSheet.Paste SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=CopyToRng, Unique:=False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advanced Filtering Problem
Hi Russell,
1. I don't have XL2007 yet so can't "play" with your routine, but I have heard that the max dimensions of a spreadsheet have changed. So the line: resultsSheet.Range("A10:IV65536").Select could cause you a problem if your database grows from ~50000 to 65535 rows. 2. Have you tried stepping through the routine to try to pinpoint more precicely where the extra time is being spent? "RussellT" wrote: The following code executes in about 2 seconds in excel 2003 but takes 2 mintes in excel 2007. Anyone know why? The size of the database is 50,000 row by 52 columns. Public Sub FilterDbase_Click() Dim filtercontrolSheet As Worksheet Dim resultsSheet As Worksheet Set filtercontrolSheet = Sheets("FilterControl") Set resultsSheet = Sheets("Results") Application.ScreenUpdating = False resultsSheet.Activate resultsSheet.Range("A10:IV65536").Select Selection.ClearContents 'Perform Extract Dim SourceRng As Range Dim CritRng As Range Dim CopyToRng As Range filtercontrolSheet.Activate filtercontrolSheet.Range("Criterion1").Select Selection.ClearContents filtercontrolSheet.Range("Criterion2").Select Selection.Copy filtercontrolSheet.Range("A29").Select ActiveSheet.Paste Set SourceRng = Sheets("PlanData").Range("SourcePlan") Set CritRng = Sheets("FilterControl").Range("A28:AV29") Set CopyToRng = Sheets("Results").Range("A10:AQ10") filtercontrolSheet.Range("PlanHeader").Select Selection.Copy resultsSheet.Activate resultsSheet.Range("A10").Select ActiveSheet.Paste SourceRng.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=CopyToRng, Unique:=False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i use advanced filtering? | Excel Worksheet Functions | |||
Advanced Filtering | Excel Discussion (Misc queries) | |||
Advanced 'filtering' | Excel Programming | |||
Advanced filtering | Excel Worksheet Functions | |||
Advanced Filtering problem | Excel Programming |