If 0 or "" delete row: code works but very slow

Question

Question:

With the recorder and help on this forum I made a code (for a button). Column 'i' has got (from row 25) 'Pcs' or a number. My Macro finds Pcs and changes it to "" and than the macro deletes "" and 0's. lenght of the filled cells is variable, so I made 500 as 'end' but it never reaches that. If I run the macro, it works and does the job, but takes very long, especially because it has to do 500 lines..

Sub Fix()

Dim intEnd As Integer
Range("M1").Select
Cells.Replace What:="pcs", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
intEnd = 500

Range("I25").Select

Do Until ActiveCell.Row = intEnd

If Int(ActiveCell.Value) = 0 Then
Range(ActiveCell.Row & ":" & ActiveCell.Row).Delete
intEnd = intEnd - 1
Else
ActiveCell.Offset(1, 0).Select
End If

Loop
End sub

I was happy that I could make this macro with help of the forum and the recorder, but now I am stuck speeding it up, no real clue where to start. Does anybody has a tip?

Thanks, if more info or effort needed, please let me know.

@ScottCraner: thanks, I tried it with [this SO topic](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) but will review again! — bart1701, Apr 26 '16 at 14:23

Dirk Reichel · Answer 1 · 2016-04-27T13:45:01.483

3

To do it in a fast use able way, you can use this:

Sub DelMe()
  Dim i As Long, x As Variant, y As Range
  With Sheets("Sheet1")
    x = .Range("I1", .Cells(Rows.Count, 9).End(xlUp)).Value
    If UBound(x) < 25 Then Exit Sub
    For i = 25 To UBound(x)
      If x(i, 1) = 0 Or x(i, 1) = "" Or InStr(1, x(i, 1), "pcs", vbTextCompare) > 0 Then
        If y Is Nothing Then
          Set y = .Rows(i)
        Else
          Set y = Union(y, .Rows(i))
        End If
      End If
    Next
    y.EntireRow.Delete xlUp
  End With
End Sub

It simply deletes all ranges (which you want to be deleted) at once.

If you have any questions, just ask :)

edited Apr 27 '16 at 13:45

answered Apr 26 '16 at 14:54

Dirk Reichel

7,989
1
15
31

thanks for your effort, I get a "run-time error 91', "object variable with block variable not set".. y.EntireRow.Delete xlUp is the problem they say? any advise? not very skilled with array. – bart1701 Apr 27 '16 at 09:34
Oh sorry... `Set y = Intersect(y, .Rows(i))` should be `Set y = Union(y, .Rows(i))`... big mistake – Dirk Reichel Apr 27 '16 at 13:46

score 1 · Accepted Answer · answered Apr 26 '16 at 14:25

1

Try this:

Sub fix3()


Dim intEnd As Long
Dim ws As Worksheet
Dim i As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

On Error GoTo getout
Set ws = Sheets("Sheet1") 'Change to your sheet
ws.Cells.Replace What:="pcs", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

intEnd = ws.Range("I" & ws.Rows.Count).End(xlUp).row

For i = intEnd To 25
    If Int(ws.Cells(i, "I").Value) = 0 Then
        ws.Rows(i).Delete
    End If
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

getout:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

answered Apr 26 '16 at 14:25

Scott Craner

148,073
10
49
81

Curiosity question about toggling the automatic calculation. So you turn off automatic calculation...which I kind of like. You manipulate your arbitrary 1 million cells of data with some code, and at the very end you turn you automic calc back on. When its turned back on does it automatically do a calculation? or do you need to do something in a cell to trigger an autocalc to occur? – Forward Ed Apr 26 '16 at 14:29
you know, I do not know the answer to that question. I would assume it would wait for the next time that calculation is warranted. – Scott Craner Apr 26 '16 at 14:32
might need to tac on an application.calc or whatever the VBA code is at the end. – Forward Ed Apr 26 '16 at 14:36
@ScottCraner thanks for your help, your code somehow did not work properly, but I used the Application.Calculation = xlCalculationAutomatic.. now it takes 1 second in stead of 60! so this is way better that it was! thanks ! – bart1701 Apr 26 '16 at 14:43
@bart1701 what did it do wrong? What error? I could not test it. – Scott Craner Apr 26 '16 at 14:44
1

maybe because any text will error at `If Int(ws.Cells(i, "I").Value) = 0 Then` and the sub will be exited then... (just a guess) – Dirk Reichel Apr 26 '16 at 14:59
@DirkReichel I assumed that since it was in the OP that it would not return an error. But I like yours better. – Scott Craner Apr 26 '16 at 15:00

If 0 or "" delete row: code works but very slow

2 Answers2

Linked