asked on

VBA code shorten run time

The code below works to remove lines in column E that have "Unkn".
It takes a very long time to run - is there any way to modify to run more efficiently?

THANK YOU!

Sub delnozone()
Application.ScreenUpdating = False
Shipping.Activate


    Dim i As LongPtr
    i = 2
    Do Until i > Cells(Cells.Rows.Count, "A").End(xlUp).Row
        If Replace(Cells(i, "e").Text, Chr(32), "") = "" Or Cells(i, "e").Text = "Unkn" Then
            Rows(i).Delete
        Else
            i = i + 1
        End If
    Loop
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rgonzo1971

Hi,

pls try

Sub delnozone()
Application.ScreenUpdating = False
Shipping.Activate


Range("A1").EntireColumn.Insert
Range(Range("B1"), Range("B" & Rows.Count).End(xlUp)).Offset(, -1).Formula = "=IF(OR(TRIM(F1)="""",F1=""Unkn""),NA(),1)"
Range("A1").EntireColumn.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Range("A1").EntireColumn.Delete
    
End Sub

Open in new window

Regards

Euro5

ASKER

Wow so fast!!! Thanks you!!

Martin Liss

You're welcome and I'm glad I was able to help.

Expand my profile’s “Full Biography” and you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015