mae0429
06-25-2008, 08:20 AM
Hi all,
I took a look at the "optimizing your code" article and made some changes in the other parts of my code which have really helped: to get to this point it only takes 5 seconds now (which is about 20 seconds faster :thumb ). I'm stuck at these 2 FOR loops, which are slowing things down considerably (this piece of code takes me about 13 seconds, but is repeated 7 times with slight variations in the code each time. The number of columns is 105 (right now, could be more) and the number of rows is 1013 (which will definitely be more in the future).
'**********************************************
Dim Start As Double, Finish As Double
Start = Timer
'**********************************************
'Create individual product and root cause scope sheets by sorting raw data page
Worksheets("RAW CAPA DATA").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Worksheets("ICDs").Select
ActiveSheet.Paste
For i = 1 To LastColumn
Select Case i
Case 1, 11, 12, 15, 16, 17, 18, 22, 28, 36, 37, 85
Columns(i).EntireColumn.AutoFit
Case Else
Columns(i).Hidden = True
End Select
Next i
For j = 2 To LastRow
Rows(j).RowHeight = 69.75
Next j
Range("CH2").Select
Selection.AutoFilter Field:=86, Criteria1:="ICD"
Range("A1:CG1").Select
With Selection
.HorizontalAlignment = xlCenter
.Merge
End With
ActiveCell.FormulaR1C1 = "RAW CAPA DATA - ICDs"
ActiveWindow.Zoom = 75
'**********************************************
Finish = Timer
MsgBox "Run time was " & Finish - Start
'**********************************************
I can also take out the ".Select" followed by "Selection." pieces right?
Thanks for any help!
-Matt
I took a look at the "optimizing your code" article and made some changes in the other parts of my code which have really helped: to get to this point it only takes 5 seconds now (which is about 20 seconds faster :thumb ). I'm stuck at these 2 FOR loops, which are slowing things down considerably (this piece of code takes me about 13 seconds, but is repeated 7 times with slight variations in the code each time. The number of columns is 105 (right now, could be more) and the number of rows is 1013 (which will definitely be more in the future).
'**********************************************
Dim Start As Double, Finish As Double
Start = Timer
'**********************************************
'Create individual product and root cause scope sheets by sorting raw data page
Worksheets("RAW CAPA DATA").Activate
ActiveSheet.Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Worksheets("ICDs").Select
ActiveSheet.Paste
For i = 1 To LastColumn
Select Case i
Case 1, 11, 12, 15, 16, 17, 18, 22, 28, 36, 37, 85
Columns(i).EntireColumn.AutoFit
Case Else
Columns(i).Hidden = True
End Select
Next i
For j = 2 To LastRow
Rows(j).RowHeight = 69.75
Next j
Range("CH2").Select
Selection.AutoFilter Field:=86, Criteria1:="ICD"
Range("A1:CG1").Select
With Selection
.HorizontalAlignment = xlCenter
.Merge
End With
ActiveCell.FormulaR1C1 = "RAW CAPA DATA - ICDs"
ActiveWindow.Zoom = 75
'**********************************************
Finish = Timer
MsgBox "Run time was " & Finish - Start
'**********************************************
I can also take out the ".Select" followed by "Selection." pieces right?
Thanks for any help!
-Matt