Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

VBA replace too long to run

My code is taking too long to run on a large data set.
Can someone help improve by making it more efficient?
All it does is look for errors in either u,v,w or x and delete the row.
First it is 'trimming', because it was not recognizing errors at all, until I used the trim characters.
I appreciate any help!

Sub delerrors()
Application.ScreenUpdating = False
Reduced_Data.Activate


    Dim i As Long
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Replace(Cells(i, "u").Text, Chr(32), "") = "#REF!" Or _
           Replace(Cells(i, "u").Text, Chr(32), "") = "#N/A" Or _
           Replace(Cells(i, "v").Text, Chr(32), "") = "#REF!" Or _
           Replace(Cells(i, "v").Text, Chr(32), "") = "#N/A" Or _
           Replace(Cells(i, "w").Text, Chr(32), "") = "#REF!" Or _
           Replace(Cells(i, "w").Text, Chr(32), "") = "#N/A" Or _
           Replace(Cells(i, "x").Text, Chr(32), "") = "#REF!" Or _
           Replace(Cells(i, "x").Text, Chr(32), "") = "#N/A" Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Euro5

ASKER

Thanks!