FASTER USE OF THE REPLACE() FUNCTION !!!!!
in [Programming]
|
From: Rick Rothstein on 27 Mar 2010 17:07 You don't have to loop the range... you can apply the range's Replace method to the entire range all at once... Range("B3:B65500").Replace "-", "," -- Rick (MVP - Excel) "JLGWhiz" <JLGWhiz(a)cfl.rr.com> wrote in message news:O$CsnCfzKHA.2016(a)TK2MSFTNGP02.phx.gbl... > 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: tompl on 27 Mar 2010 17:11 Good answer. I don't see what the if statement accomplishes. I the cell is empty no "-" would be found anyway. Tom "Mike H" wrote: > 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 *** > > . > >
From: Mike H on 27 Mar 2010 17:21 Yes, Excellent point about the IF I never noticed that. I also meant to add that with a worksheet full of formula the increase in performance will be dramatic. I filled column c with a simple formula =d1/e1 and re-ran the test and got 4.2 seconds runtime with calculation/screenupdating disabled and 200 seconds with them enabled. Surprisingly, well to me it was, with the IF statment removed the run time only went down to 3.9 seconds but the point about removing it is still valid 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) Rng.Value = Replace(Rng.Value, "-", ",") 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. "tompl" wrote: > Good answer. I don't see what the if statement accomplishes. I the cell is > empty no "-" would be found anyway. > > Tom > > "Mike H" wrote: > > > 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 *** > > > . > > >
From: Rick Rothstein on 27 Mar 2010 17:23 First off, I would highly suggest the method I posted be used as it will be much, much faster than looping the cells. However, with that said, if one were going to use the posted loop, it should be more efficient to test the cell and only apply the Replace function call if a dash is found... the If test will execute much faster than the Replace function call will... Replace is not one of VB's faster functions and, for cells with no dash in them, it will still spend some time deciding that it has nothing to do. -- Rick (MVP - Excel) "tompl" <tompl(a)discussions.microsoft.com> wrote in message news:62CE4FEC-6724-4D48-805F-F1D0E7F48724(a)microsoft.com... > Good answer. I don't see what the if statement accomplishes. I the cell > is > empty no "-" would be found anyway. > > Tom > > "Mike H" wrote: > >> 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 *** >> > . >> >
From: Mike H on 27 Mar 2010 17:48 Rick, I'm baffled --- help. Using your suggestion and with the code below i get a runtime of ~2.4 seconds. Un-Comment the calculation and screenupdating lines to disable them and the runtime goes UP to ~3.1 seconds. Why is that happening, am i making an elementary error? Sub BlankDays() starttime = Timer 'Application.ScreenUpdating = False 'Application.Calculation = xlCalculationManual Range("B3:B65500").Replace "-", "," 'Application.ScreenUpdating = True 'Application.Calculation = xlCalculationAutomatic MsgBox "Runtime " & Timer - starttime End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Rick Rothstein" wrote: > 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 *** > > . >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Prevent clicking on a cell Next: INSERT FILEDATE INTO CELL COMMENT |