Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / December 2006

Tip: Looking for answers? Try searching our database.

Code runs slooooww

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
GettingThere - 14 Dec 2006 01:53 GMT
The following code takes many, many minutes to run on several hundred rows.  
Does anyone have any suggestions that might speed it up?

Thanks in advance!

Option Explicit

Sub tester()
Dim i As Long
Dim area As String
Dim c As Range
Const sked = "0:30"

Application.ScreenUpdating = False

For i = 3 To Cells(Rows.Count, "C").End(xlUp).Row Step 1
 
If Cells(i, 1).Value <> "CT" Then
 
  area = Cells(i, "D").Value
   
Select Case area
  Case "CR1"
     For Each c In Range(Cells(i, "u"), Cells(i, "bg"))
           If c.Text Like sked Then
           c.Value = "CR1"
           Else
           c.Value = "X"
           End If
     Next c

  Case "CR2", "CR3"
     If Cells(i, "E").Value Like "*CR1*" Then
           For Each c In Range(Cells(i, "u"), Cells(i, "bg"))
           If c.Text Like sked Then
           c.Value = "OTH"
           Else
           c.Value = "X"
          End If
     Next c
    Else
     Range(Cells(i, "u"), Cells(i, "bg")).Value = "X"
     End If
 Case Else
     Cells(i, "U").Value = "X"
   
End Select

Else
Range(Cells(i, "u"), Cells(i, "BG")).Value = "X"
 ' GO TO NEXT
 
End If
 
Next i
End Sub
Gary Keramidas - 14 Dec 2006 02:03 GMT
try turning calculation off right after screenupdating

Application.Calculation = xlCalculationManual

and back on at the end

Application.Calculation = xlCalculationAutomatic

Signature

Gary

> The following code takes many, many minutes to run on several hundred rows.
> Does anyone have any suggestions that might speed it up?
[quoted text clipped - 52 lines]
> Next i
> End Sub
GettingThere - 15 Dec 2006 00:28 GMT
Thanks Gary - that did the trick.  It's funny because I had done this very
thing in a different part of the module, but I didn't think it would do any
good here since I'm not working with formulas.  Learn something new every day
: )

> try turning calculation off right after screenupdating
>
[quoted text clipped - 60 lines]
> > Next i
> > End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.