Removing Rows - Code Too Slow

Question

I have the following which I am using to remove all rows of data where the name in column DX is not a specified name. The code will sort through all rows and delete each row that doesn't contain that specific name. The problem is, it is way too slow. Any thoughts?

 Sub DeleteNonName()


 Dim Firstrow As Long
 Dim Lastrow As Long
 Dim Lrow As Long
 Dim CalcMode As Long
 Dim ViewMode As Long

 With Application
     CalcMode = .Calculation
     .Calculation = xlCalculationManual
     .ScreenUpdating = False
 End With

 With ActiveSheet
     .Select
     ViewMode = ActiveWindow.View
     ActiveWindow.View = xlNormalView
     .DisplayPageBreaks = False
    Firstrow = 2
     Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

     For Lrow = Lastrow To Firstrow Step -1
         With .Cells(Lrow, "DX")
             If Not IsError(.Value) Then
                 If InStr(.Value, "Name") = 0 Then .EntireRow.Delete
             End If
         End With
      Next Lrow
     End With

 ActiveWindow.View = ViewMode
 With Application
     .ScreenUpdating = True
     .Calculation = CalcMode
 End With
End Sub

Please refer to [this](https://stackoverflow.com/q/30959315/7690982), the part of the code that takes most time is the deleting task. So also read [Excel VBA performance issues comparing row to column delete](https://stackoverflow.com/q/46163851/7690982) and [Improving a Loop to delete rows in excel faster](https://stackoverflow.com/q/46077673/7690982). So you should make the delete task just once on your code, it is the fastest way. — danieltakeshi, Oct 17 '17 at 17:06
This question is better suited for CodeReview. That is where you will get help with code that works that needs to be optimized. — Scott Craner, Oct 17 '17 at 17:14
@ScottCraner Aye, except it's always the same answer - quit using `Select`, don't iterate `Range`, use arrays instead; work out which rows you want to delete, merge them into a signle `Range`, and delete that merged range in one single operation. Total worksheet reads: 1. Total worksheet writes: 1. — Mathieu Guindon, Oct 17 '17 at 17:23
@Mat'sMug that may be and is inevitably correct, but that still does not make this a proper question for this site. As is, it is too broad. — Scott Craner, Oct 17 '17 at 17:33
@Mat'sMug, you sayed : "merge them into a signle Range". Can we merge rows even if they are in separated rows? like, merge row 1 and row 4? — D. O., Oct 17 '17 at 18:43
@D.O. he is not talking about cell merge, but using `Union()` to create one range object that gets deleted at once. — Scott Craner, Oct 17 '17 at 18:44
@D.O. You know how you can hold down `Ctrl` and select multiple disjointed rows/cells in Excel? In VBA land, `Union` does that. — Mathieu Guindon, Oct 17 '17 at 18:44
are you able to sort the worksheet on the DX column? if yes, how long does it take to execute the sort? — jsotola, Oct 18 '17 at 02:26
I have actually solved my problem with a separate code. Should I delete this post or post my solution? — NeilD137, Oct 18 '17 at 13:17
@NeilD137 Post you solution, so you can help other people with the same problem. — danieltakeshi, Oct 18 '17 at 13:31
`> Sub DeleteNotNAME() > With Range("X2", Cells(Rows.Count, "X").End(xlUp)) > If ActiveSheet.FilterMode Then .AutoFilter` > .AutoFilter Field:=128, Criteria1:="<>NAME" > On Error Resume Next > .SpecialCells(xlVisible).EntireRow.Delete > On Error GoTo 0 > If ActiveSheet.FilterMode Then .AutoFilter > End With > End Sub` — NeilD137, Oct 18 '17 at 15:03

score 0 · Accepted Answer · answered Oct 23 '17 at 15:57

Sub DeleteName() With ActiveSheet .AutoFilterMode = False With Range("DX1", Range("DX" & Rows.Count).End(xlUp)) .AutoFilter 1, "Name" On Error Resume Next .Offset(1).SpecialCells(12).EntireRow.Delete End With .AutoFilterMode = False End With Selection.AutoFilter End Sub

Removing Rows - Code Too Slow

1 Answers1