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
|
Similar Threads
