Slow Loop Help!

robfo0

Active Member
Joined
Feb 19, 2002
Messages
281
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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
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

Active Member
Joined
Feb 19, 2002
Messages
281
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!
 

Forum statistics

Threads
1,144,220
Messages
5,723,091
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top