Five plus minutes to execute
 

Sorry for the double post! I accidentally posted in general questions but I
could not figure out how to remove that post or move it into this section.

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

Five plus minutes to execute
 

Don't check more cells than needed. AND, do you really need to check colums
b:k. Lots of cells.

lastrow=cells(rows.count,"b").end(xlup).row
For Each cell In Range("B7:K500")
if Left(cell.Value, 1) = "," then cell.Value = Right(cell.Value,
Len(cell.Value) - 1)
Next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CB" wrote in message
...
Sorry for the double post! I accidentally posted in general questions but
I
could not figure out how to remove that post or move it into this section.

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...



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

Five plus minutes to execute
 

I'm not as expert as some of the others here, but I usually find it takes a
lot longer to update a cell than to check its contents. How many of these
4940 cells have leading commas, and how many commas are typical?

If more than a handful of them have two or more leading commas, it might be
a lot faster to write yourself a quick routine to remove all the commas and
THEN put the value back in the cell. Like this, maybe:

' Left-trim all of a specified character
Function LTrimC(Victim, Optional Char) As String
If IsMissing(Char) Then Char = " " Else Char = Left(Char & " ", 1)
lv = Len(Victim)
For ip = 1 To lv
If Mid(Victim, ip, 1) < Char Then Exit For
Next ip
If ip lv Then ip = 1
LTrimC = Mid(Victim, ip)
End Function

This is one I'd put in my common library and keep for next time...only I'd
name it Strip, after the REXX function, and instead of having a separate
Trim, LTrim and RTrim I'm just have Strip with a parm that indincates (L)eft,
(R)ight or (B)oth.

Anyway, having stripped all leading commas you could put it back just once,
rather than each time it found one of a number of successive commas.

But how likely is it that you actually have lots of leading commas? I'm
guessing something else is going on, and you're going to have to find it.
Unless this is only one small part of the code that's taking up the five
minutes, I submit that you should start inserting Timer and MsgBox calls here
and there in your loop and try to figure out exactly where it's taking more
time than it should.

And it could be that when you do that, you'll find out almost immediately
that it isn't slowing down at all, it's just freezing up somewhere -- find
out by the fact that you're not even getting your MsgBox messages back.

--- "CB" wrote:
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.

Five plus minutes to execute
 

I do actually need to check all of those cells (unless there is a smarter
way!). Each time I get new source data, the data will occupy different cells
but within that range, that is why I check each cell within that range.

Can you explain to me

lastrow=cells(rows.count,"b").end(xlup).row

I do not understand what that does

Thanks


"Don Guillett" wrote:


Don't check more cells than needed. AND, do you really need to check colums
b:k. Lots of cells.

lastrow=cells(rows.count,"b").end(xlup).row
For Each cell In Range("B7:K500")
if Left(cell.Value, 1) = "," then cell.Value = Right(cell.Value,
Len(cell.Value) - 1)
Next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CB" wrote in message
...
Sorry for the double post! I accidentally posted in general questions but
I
could not figure out how to remove that post or move it into this section.

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...



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

Five plus minutes to execute
 

just wondering if you've turned screenupdating and calculation off.

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

--


Gary


"CB" wrote in message
...
Sorry for the double post! I accidentally posted in general questions but I
could not figure out how to remove that post or move it into this section.

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

Five plus minutes to execute
 

It gets the row number of the last cell with data in it in Column B. If you
want the last row in Column C that has data in it, change the "B" (well, "b"
in Don's code) to "C". This code gives you a way of finding where to stop
processing at so you don't end up trying to process every cell in a column.

Rick


"CB" wrote in message
...
I do actually need to check all of those cells (unless there is a smarter
way!). Each time I get new source data, the data will occupy different
cells
but within that range, that is why I check each cell within that range.

Can you explain to me

lastrow=cells(rows.count,"b").end(xlup).row

I do not understand what that does

Thanks


"Don Guillett" wrote:


Don't check more cells than needed. AND, do you really need to check
colums
b:k. Lots of cells.

lastrow=cells(rows.count,"b").end(xlup).row
For Each cell In Range("B7:K500")
if Left(cell.Value, 1) = "," then cell.Value = Right(cell.Value,
Len(cell.Value) - 1)
Next

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"CB" wrote in message
...
Sorry for the double post! I accidentally posted in general questions
but
I
could not figure out how to remove that post or move it into this
section.

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...



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

Five plus minutes to execute
 

Thanks for the replies Bob and Gary,

Since I accidently posted this is two sections (I meant to post here but
posted in general first) someone from the other section gave me that tip and
my code sped up tremendously, from over five minutes to under 10 seconds. He
did also tweak my code a little bit so I am not sure how much of the
improvement is from turning off the screen updating but, my guess is most of
it.


"Gary Keramidas" wrote:

just wondering if you've turned screenupdating and calculation off.

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

--


Gary


"CB" wrote in message
...
Sorry for the double post! I accidentally posted in general questions but I
could not figure out how to remove that post or move it into this section.

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

Five plus minutes to execute
 

To be technically correct, it gets not (necessarily) the last cell with data
in it, but the last cell before the next blank cell. Ricks code is the
equivalent of you hitting <Ctl-End, selecting the cell in column B of that
row, and then hitting <End<Up. If you have 1000 rows of data with blank
rown at 239 and 530, you'll find your cursor on B531 by either method. So
using the End method is really handy -- as long as you don't lose track of
whether you have any blank areas in your data.

--- "Rick Rothstein (MVP - VB)" wrote:
It gets the row number of the last cell with data in it in Column B. If you
want the last row in Column C that has data in it, change the "B" (well, "b"
in Don's code) to "C". This code gives you a way of finding where to stop
processing at so you don't end up trying to process every cell in a column.

--- "CB" wrote:
I do actually need to check all of those cells (unless there is a smarter
way!). Each time I get new source data, the data will occupy different
cells but within that range, that is why I check each cell within that range.

Can you explain to me

lastrow=cells(rows.count,"b").end(xlup).row

--- "Don Guillett" wrote:
Don't check more cells than needed. AND, do you really need to check
colums b:k. Lots of cells.

lastrow=cells(rows.count,"b").end(xlup).row
For Each cell In Range("B7:K500")
if Left(cell.Value, 1) = "," then cell.Value = Right(cell.Value,
Len(cell.Value) - 1)
Next

--- "CB" wrote:
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... 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.

Five plus minutes to execute
 

If your data is bounded by blank rows and columns, you can use
Range("B7").CurrentRegion instead of searching for the bottom row.

Also, these operations are much faster in memory, so pull the range
into an array, change the array, and put it back:
Dim ary as Variant, x as Long, y as Long
ary = Range("B7").CurrentRegion
for x = lbound(ary,1) to ubound(ary,1)
for y = lbound(ary,2) to ubound(ary,2)
if left(ary(x,y),1 = "," then ary(x,y) = mid(ary(x,y),2)
next y
next x
Range("B7").CurrentRegion = ary

Carl.


On Aug 25, 5:55*pm, Bob Bridges
wrote:
To be technically correct, it gets not (necessarily) the last cell with data
in it, but the last cell before the next blank cell. *Ricks code is the
equivalent of you hitting <Ctl-End, selecting the cell in column B of that
row, and then hitting <End<Up. *If you have 1000 rows of data with blank
rown at 239 and 530, you'll find your cursor on B531 by either method. *So
using the End method is really handy -- as long as you don't lose track of
whether you have any blank areas in your data.

--- "Rick Rothstein (MVP - VB)" wrote:



It gets the row number of the last cell with data in it in Column B. If you
want the last row in Column C that has data in it, change the "B" (well, "b"
in Don's code) to "C". This code gives you a way of finding where to stop
processing at so you don't end up trying to process every cell in a column.

--- "CB" wrote:
I do actually need to check all of those cells (unless there is a smarter
way!). Each time I get new source data, the data will occupy different
cells but within that range, that is why I check each cell within that range.

Can you explain to me

lastrow=cells(rows.count,"b").end(xlup).row

--- "Don Guillett" wrote:
Don't check more cells than needed. AND, do you really need to check
colums b:k. Lots of cells.

lastrow=cells(rows.count,"b").end(xlup).row
For Each cell In Range("B7:K500")
* *if Left(cell.Value, 1) = "," then *cell.Value = Right(cell.Value,
Len(cell.Value) - 1)
Next
--- "CB" wrote:
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... 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.- Hide quoted text -

- Show quoted text -