Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks

Every time you change a cell value, the whole workbook recalculates.
I'd need to know what the code is supposed to do and why, to suggest
something.

Best wishes Harald


"CB" wrote in message
...
Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep
giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks

The code runs through each cell in the specified range and checks the first
character in the string and If that character is a "," then it is deleted.

There are instances where there may be multiple commas before any actual
text so it runs until no comma precedes text.

Example:
I may have ",,,Blue,,Green,Yellow" In one cell

After this portion of code is run I only have "Blue,,Green,Yellow" all of
the commas in the beginning have been removed (commas later in the string are
OK)

Hopefully that is a good enough explanation


"Harald Staff" wrote:

Every time you change a cell value, the whole workbook recalculates.
I'd need to know what the code is supposed to do and why, to suggest
something.

Best wishes Harald


"CB" wrote in message
...
Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep
giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks

This will make it quicker

Application.ScreenUpdating = False
For Each cell In Range("B7:K500")
Data = cell.value
Do While Left(Data, 1) = ","
Data = Right(Data, Len(Data) - 1)
Loop
cell.value = Data
Next
Application.ScreenUpdating = True

"CB" wrote:

The code runs through each cell in the specified range and checks the first
character in the string and If that character is a "," then it is deleted.

There are instances where there may be multiple commas before any actual
text so it runs until no comma precedes text.

Example:
I may have ",,,Blue,,Green,Yellow" In one cell

After this portion of code is run I only have "Blue,,Green,Yellow" all of
the commas in the beginning have been removed (commas later in the string are
OK)

Hopefully that is a good enough explanation


"Harald Staff" wrote:

Every time you change a cell value, the whole workbook recalculates.
I'd need to know what the code is supposed to do and why, to suggest
something.

Best wishes Harald


"CB" wrote in message
...
Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep
giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks

Excellent solution, worked like a charm, thanks!

I am new to Macros, can you tell me why this way is so much quicker?


"Joel" wrote:

This will make it quicker

Application.ScreenUpdating = False
For Each cell In Range("B7:K500")
Data = cell.value
Do While Left(Data, 1) = ","
Data = Right(Data, Len(Data) - 1)
Loop
cell.value = Data
Next
Application.ScreenUpdating = True

"CB" wrote:

The code runs through each cell in the specified range and checks the first
character in the string and If that character is a "," then it is deleted.

There are instances where there may be multiple commas before any actual
text so it runs until no comma precedes text.

Example:
I may have ",,,Blue,,Green,Yellow" In one cell

After this portion of code is run I only have "Blue,,Green,Yellow" all of
the commas in the beginning have been removed (commas later in the string are
OK)

Hopefully that is a good enough explanation


"Harald Staff" wrote:

Every time you change a cell value, the whole workbook recalculates.
I'd need to know what the code is supposed to do and why, to suggest
something.

Best wishes Harald


"CB" wrote in message
...
Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep
giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks

Joel,

One more question for you. I have noticed that when I run this macro with
different workbooks the speed in which the code executes is affected. For
example, if I run this code in my personal workbook there is no issue
however, if I run the code from the work which I intended to run the macro
once I have completed it, it is significantly slower.

It would also be important to note that the code prior to this small snippet
is opening an external workbook performing the task then closing it. I just
don't understand why the code would run at different speeds depending on
where I run it from...any thoughts?


"Joel" wrote:

This will make it quicker

Application.ScreenUpdating = False
For Each cell In Range("B7:K500")
Data = cell.value
Do While Left(Data, 1) = ","
Data = Right(Data, Len(Data) - 1)
Loop
cell.value = Data
Next
Application.ScreenUpdating = True

"CB" wrote:

The code runs through each cell in the specified range and checks the first
character in the string and If that character is a "," then it is deleted.

There are instances where there may be multiple commas before any actual
text so it runs until no comma precedes text.

Example:
I may have ",,,Blue,,Green,Yellow" In one cell

After this portion of code is run I only have "Blue,,Green,Yellow" all of
the commas in the beginning have been removed (commas later in the string are
OK)

Hopefully that is a good enough explanation


"Harald Staff" wrote:

Every time you change a cell value, the whole workbook recalculates.
I'd need to know what the code is supposed to do and why, to suggest
something.

Best wishes Harald


"CB" wrote in message
...
Can anyone tell me why this piece of code is taking over five minutes to
execute. I actually don't know the total time it takes because I keep
giving
up after waiting about five minutes...


For Each cell In Range("B7:K500")
Do While Left(cell.Value, 1) = ","
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Loop
Next

If anyone could tell me a more efficient way to write the code I would
appreciate it. This is a small part of a larger macro that I would like to
run in under one minute.

Thanks