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

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