Excel VBA deleting rows based on an if statement (speed up)

Question

I am deleting rows based on the value in column P. Cells in column P have an if statement: IF(K<10,0,1)

If the value in column P is 0, then the row needs to be deleted. I am using the following macro which works but takes quite long. I would like to beable to process about 10000 rows.

It would be much appreciated if I could have some suggestions on speeding up this code.

[I had tried using this if statement: IF(K<10,"",1) And then deleting rows using SpecialCells(XlCellTypeBlanks) but the the cells are not interpreted as blank , due to the presence of the formula I presume. ]

 Sub RemoveBlankRows()

   Application.ScreenUpdating = False
    'PURPOSE: Deletes any row with 0 cells located inside P
    'Reference: www.TheSpreadsheetGuru.com

   Dim rng As Range
   Dim blankrng As Range
   Dim cell As Range

   'Store blank cells inside a variable
   'On Error GoTo NoBlanksFound
Set rng = Range("P2:P30000") '.SpecialCells(xlCellTypeBlanks)
'On Error GoTo 0

For Each cell In rng
If cell.Value = 0 Then
    cell.EntireRow.Delete
    'Value = ""
End If
Next

Application.ScreenUpdating = True
End Sub

What about sorting? Can you sort your table/data, in a way that groups your 0 cells together, then just grab the first row and last row with a 0, and delete the range? — BruceWayne
– BruceWayne, Commented Oct 24, 2016 at 14:17

Gary's Student · Accepted Answer · 2016-10-24 14:36:17Z

0

This looks for 0 and avoids blanks:

Sub RowKiller()
    Dim rKill As Range, r As Range, rng As Range

    Set rng = Range("P2:P30000")
    Set rKill = Nothing

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

    For Each r In rng
        If r.Value = 0 And r.Value <> "" Then
            If rKill Is Nothing Then
                Set rKill = r
            Else
                Set rKill = Union(rKill, r)
            End If
        End If
    Next r

    If Not rKill Is Nothing Then rKill.EntireRow.Delete

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub

This is only demo code. Tailor it to meet your needs.

answered Oct 24, 2016 at 14:36

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

E Kadwa Over a year ago

Thank you very much

E Kadwa Over a year ago

Works perfectly

Collectives™ on Stack Overflow

Excel VBA deleting rows based on an if statement (speed up)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related