CODEWITHSUNDEEP
excelvba
ShadowLiberal
ShadowLiberal

Reputation: 348

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

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

Upvotes: 2

Views: 38070

Answers (3)

user7333687
user7333687

Reputation: 1

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

Upvotes: 0

ShadowLiberal
ShadowLiberal

Reputation: 348

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

Upvotes: 2

bf2020
bf2020

Reputation: 732

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

Upvotes: 0

Related Questions