FASTER USE OF THE REPLACE() FUNCTION !!!!!
in [Programming]
|
From: jay dean on 27 Mar 2010 16:06 Hi - I am using the Replace() function for a very large range. It works okay but wait time is quite long. Is there a more elegant approach to the below? For Each rng In Range("B3:B65500") If rng.Value <> "" Then rng.Value = Replace(rng.Value, "-", ",") End If Next rng Any help would be appreciated! Thanks Jay *** Sent via Developersdex http://www.developersdex.com ***
From: Rick Rothstein on 27 Mar 2010 16:55 Try it this way... Range("B3:B65500").Replace "-", "," -- Rick (MVP - Excel) "jay dean" <fresh1700(a)yahoo.com> wrote in message news:utNlBkezKHA.3264(a)TK2MSFTNGP06.phx.gbl... > Hi - > > I am using the Replace() function for a very large range. It works okay > but wait time is quite long. Is there a more elegant approach to the > below? > > For Each rng In Range("B3:B65500") > If rng.Value <> "" Then > rng.Value = Replace(rng.Value, "-", ",") > End If > Next rng > > > Any help would be appreciated! Thanks > > Jay > > *** Sent via Developersdex http://www.developersdex.com ***
From: Rick Rothstein on 27 Mar 2010 16:59 I just wanted to mention as a follow up that I'm using the Replace method of a range rather than VB's Replace function... it will be much faster as you don't have to loop the cells individually. -- Rick (MVP - Excel) "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message news:u0Ool$ezKHA.5940(a)TK2MSFTNGP02.phx.gbl... > Try it this way... > > Range("B3:B65500").Replace "-", "," > > -- > Rick (MVP - Excel) > > > > "jay dean" <fresh1700(a)yahoo.com> wrote in message > news:utNlBkezKHA.3264(a)TK2MSFTNGP06.phx.gbl... >> Hi - >> >> I am using the Replace() function for a very large range. It works okay >> but wait time is quite long. Is there a more elegant approach to the >> below? >> >> For Each rng In Range("B3:B65500") >> If rng.Value <> "" Then >> rng.Value = Replace(rng.Value, "-", ",") >> End If >> Next rng >> >> >> Any help would be appreciated! Thanks >> >> Jay >> >> *** Sent via Developersdex http://www.developersdex.com *** >
From: JLGWhiz on 27 Mar 2010 17:01 Would this not work: For Each rng In Range("B3:B65500") rng.Replace "-", "," Next rng "jay dean" <fresh1700(a)yahoo.com> wrote in message news:utNlBkezKHA.3264(a)TK2MSFTNGP06.phx.gbl... > Hi - > > I am using the Replace() function for a very large range. It works okay > but wait time is quite long. Is there a more elegant approach to the > below? > > For Each rng In Range("B3:B65500") > If rng.Value <> "" Then > rng.Value = Replace(rng.Value, "-", ",") > End If > Next rng > > > Any help would be appreciated! Thanks > > Jay > > *** Sent via Developersdex http://www.developersdex.com ***
From: Mike H on 27 Mar 2010 17:07
Hi, First let's make sure were not doing too many rows by making the range dynamic by finding the last row. Then stop the screen updating and the worksheet calculating. Doing this on my pc reduced the runtime from 11 seconds to less than 4 Sub BlankDays() Dim LastRow As Long LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row Application.ScreenUpdating = False Application.Calculation = xlCalculationManual For Each Rng In Range("B3:B" & LastRow) If Rng.Value <> "" Then Rng.Value = Replace(Rng.Value, "-", ",") End If Next Rng Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "jay dean" wrote: > Hi - > > I am using the Replace() function for a very large range. It works okay > but wait time is quite long. Is there a more elegant approach to the > below? > > For Each rng In Range("B3:B65500") > If rng.Value <> "" Then > rng.Value = Replace(rng.Value, "-", ",") > End If > Next rng > > > Any help would be appreciated! Thanks > > Jay > > *** Sent via Developersdex http://www.developersdex.com *** > . > |