Slow Loop Help!

[robfo0]

I have the following loop which currently only runs through 500 cells, but for some reason it seems to be taking FOREVER (30 seconds or more) to run. it seems like it should be a very fast process. I was wondering if someone might be able to help me optimize it or change it to make it faster. Here is the code:

SavedCells = ThisWorkbook.Sheets("DataSheet").Range("A65536").End(xlUp).Row
Counter = 1
Do Until Counter = SavedCells + 1
If Left(ThisWorkbook.Sheets("DataSheet").Range("A" & Counter).Value, 3) = "***" Or _
Left(ThisWorkbook.Sheets("DataSheet").Range("A" & Counter).Value, 3) = "" Then
Else
ThisWorkbook.Names.Add Name:=ThisWorkbook.Sheets("DataSheet").Range("A" & Counter).Value, _
RefersToR1C1:=ThisWorkbook.Sheets("DataSheet").Range("B" & Counter)
End If
Counter = Counter + 1
Loop

any help is appreciated, thanks!

 

[stevebausch]

Some guesses:

Set Application.EnableEvents = False before your code starts. Your code may be triggering events, and those events may have code in them, which triggers ANOTHER event, which then......... you get the idea?

Not that you are using a For..Next loop, but if you were doing so, you should declare your variables to the exact type desired, using a Variant for a For...Next loop variable will waste time; use an Integer.

Also, what do you think your code is doing? I suspect what I see in your code is NOT what you think the code is doing.

So, before we go any further, tell us all what the code should be doing, step by step by step.

Steve
This message was edited by stevebausch on 2002-09-27 16:32

 

[robfo0]

i know what the code is doing. It is looping through column A, and creating a defined name for the corresponding B cell, using A's value as the name (unless A is blank or begins with "***"). This is what i want it to do, but ofcourse this is only a snipet of a much larger macro. I just noticed this is the part that was taking much longer than anything else. I will try setting enable events to false and see if that helps

Thanks!