Using Excel Macros to replace text quickly if the cell is a certain value

Question

I have a macro that's met to find all the rows in the N column in an excel spreadsheet with a value of 'Accept', and adjust their value to 'Reject'.

My macro is working, but it works VERY slow, it literally took me over 15 minutes for my macro to run through 20,000+ rows changing the cell value from Accept to Reject, which is way too long for me to expect any customer to wait (20,000 is the high end of how many rows of data I'd expect customers to have).

Below is the code in my macro, I'm wondering if anyone has any ideas how I can make it run faster.

' Select cell N2, *first line of data*.
Range("N2").Select
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
    If ActiveCell.Value = "Accept" Then
        ActiveCell.Value = "Reject"
    End If
    ' Step down 1 row from present location.
    ActiveCell.Offset(1, 0).Select
Loop

Consider recording a macro that does a Find and Replace on Column N and adjusting as needed. — Doug Glancy, Nov 26 '13 at 20:34
Also, avoid using `Select`. http://stackoverflow.com/q/10714251/293078 — Doug Glancy, Nov 26 '13 at 20:36
agree with @DougGlancy and :@pnuts here. `.Replace` would be must faster. here is an example http://stackoverflow.com/questions/10075968/using-the-replace-method-in-vba-leaves-numbers-stored-as-strings — Siddharth Rout, Nov 26 '13 at 20:38
Another one http://stackoverflow.com/questions/20154207/how-do-i-remove-a-character-from-a-column-of-data-in-excel — Siddharth Rout, Nov 26 '13 at 20:39
the real problem is the screen updating, check my answer that really makes it faster, because in your code screen is drawing and drawing each time you modify a cell value. — bto.rdz, Nov 26 '13 at 20:40
if you need a loop then try to use something like for `i = 1 to 20000` then `cells(i,14)`, **WITHOUT select**, will sure speed things up, also bto.rdz has a point to trun off the screen updating. — Takedasama, Nov 26 '13 at 20:42
yeah, im sure without select you will save 10% of process time, but the real problem in screen udating im sure that will save more than 50% of your process time. — bto.rdz, Nov 26 '13 at 20:48

score 2 · Accepted Answer · answered Nov 27 '13 at 15:23

Thanks for all the help guys. I used some of the links and code you guys posted (especially the link Doug Glancy posted in a comment, wish I could pick comments as the accepted answer) to come up with some new code that works almost instantly. For anyone who's interested in how it's working, here's the new VBA code.

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = Range("N2:N" & ActiveSheet.UsedRange.Rows.Count)
dat = rng  ' dat is now array
For i = LBound(dat, 1) To UBound(dat, 1)
    If dat(i, 1) = "Accept" Then
        dat(i, 1) = "Reject"
    End If
Next
rng = dat ' put new values back on sheet

bf2020 · Answer 2 · 2013-11-26T21:20:16.190

0

The following has worked very fast for me in the past:

Have macro select area/range that needs to have values replaced.

Selection.Replace What:="Accept",Replacement:="Reject", LookAt:=xlPart, SearchOrder:=xlByRows,MatchCase:=True,SearchFormat:=False,ReplaceFormat:=False

edited Nov 26 '13 at 21:20

answered Nov 26 '13 at 21:06

bf2020

742
4
7

score 0 · Answer 3 · edited Dec 23 '16 at 08:32

0

Try this:

Sub formatnumbers()
    Do Until IsEmpty(ActiveCell)
    ActiveCell.Select
    ActiveCell.Replace What:=ActiveCell.Value, Replacement:=ActiveCell.Value, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 ActiveCell.Offset(2000, 0).Select
 Loop
End Sub

edited Dec 23 '16 at 08:32

theoretisch

1,718
5
24
34

answered Dec 23 '16 at 07:29

user7333687

1

Using Excel Macros to replace text quickly if the cell is a certain value

3 Answers3

Linked