Delete many rows - need an efficient, fast technique

[polarisking]

I'm trying to delete several thousand entire rows, and it's taking way too long. I've turned off screenupdating and auto calculation with noticing much improvement.

Any ideas?

 

[Luke M]

Is your data filtered? Filtered data slows things down a lot. If not, more details about what your setup is.
Is it a large data set?
Filtered?
Deleting rows 1 by 1, or a large set all at once?
If the former, what code are you using?

 

[polarisking]

Luke, the data's not filtered. I'm going traversing the data one row at a time, testing the value in column C, and depending on the result issuing a EntireRow.Delete command.

Do While Range("A" & ctrRow) <> 0
    
    If Range("C" & ctrRow) < Range("CurrentDateMinus10") Then
        Cells(ctrRow, "A").EntireRow.Delete
        Debug.Print "Row" & ctrRow
    End If
        
    ctrRow = ctrRow + 1
Loop

 

[Luke M]

Hmm. Is this fast enough?

Dim SomeValue As Double
Dim i As Long
Dim lastRow As Long
 
'Store this in a variable, so we don't have to
'keep reading the cell every time
SomeValue = Range("CurrentDateMinus10").Value
 
'Define where the last record is at
With ActiveSheet
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
 
'Step through rows backwards, since everytime we delete a row
'all the rows below will shift up a row, messing up our count
'if we started at top
Application.ScreenUpdating = False
For i = lastRow To 2 Step -1
    With Cells(i, "C")
        If .Value < SomeValue Then
            .EntireRow.Delete
            Debug.Print "Row " & i
        End If
    End With
Next i
Application.ScreenUpdating = True

Not instantaneous, but should be faster than before. How large of a dataset are you dealing with?

 

[polarisking]

Luke, your solution works but, for 7,000 rows, is very slow. I stumbled across this. It's virtually instantanous. I'm embarrassed to say I don't really know how/why it works especially with regards to the WITH construct. Thank you for your prior responses. Can you shed some some light on why this is so fast?

Sub proclearCells()
Dim rngTable    As Range
Dim ws          As Worksheet
Dim StartCell  As Range
Const ColumntoFilter1 As Integer = 3
'Turn Screen Updating off
ScreenUpdating = False
Set ws = Worksheets("RKS MV")
ws.Select
'Set the starting position (Top-left most position) of your data range
Set StartCell = ws.Range("A1")
'Turn off autofilter in case it's already active
ws.AutoFilterMode = False
'Define data table
Set rngTable = StartCell.CurrentRegion
'Filter and delete data
With rngTable
    .AutoFilter Field:=ColumntoFilter1, Criteria1:="<" & Range("CurrentDateMinus10")
    .Offset(1, 0).EntireRow.Delete
End With
'Turn filters off again
ws.AutoFilterMode = False
'Turn Screen Updating on again
ScreenUpdating = False
End Sub

 

[Luke M]

The With statement lets you shorten up your code, and saves time when writing and some time during initial compile. Works by stating some object, and then the contained lines which start with a "." refer back to that object.
So, rather than writing
ActiveSheet.Select
ActiveSheet.Copy

you can write:
With ActiveSheet
.Select
.Copy
End with

As for why it's so much faster, the code applies a single AutoFilter to your range based on the criteria you have. Since everything now showing is what you want to delete, the process goes a lot faster. The source for saving time is usually eliminating Select's, and eliminating # of times you read/write to the XL workbook.

 

[polarisking]

Thank you for all of your help.

 

[Smallman]

Hi PolariSkiing

You have happened across probably the fastest method for removing data based on a condition. Since you are running your code from a separate sheet, you don’t need to select sheets to run the code form. Just tell it what you want it to do. Additionally I don’t’ think your code will run without failing. This;

Screenupdating = False

is not going to fly. You need to precede this with Application like;

application.screenupdating = false

At the bottom of the code you have repeated the line again. Traditionally at the end of the code you put the following.

application.screenupdating = true

I am not sure how you got your date to be read by the Autofilter as dates usually give the Autofilter a huge amount of trouble. Trust me I have walked down that merry path before. With that in mind the following will work. I have attached a workbook to prove workings.

Option Explicit
 
Sub AutoMotor() 'RKS MV sheet is sheet1
    Sheet1.Range("C2", Sheet1.Range("C65536").End(xlUp)).AutoFilter 1, _
    "<" & CLng(DateValue(Range("CurrentDateMinus10")))
    Sheet1.Range("C2", Sheet1.Range("C65536").End(xlUp)).EntireRow.Delete
    Sheet1.[c1].AutoFilter
End Sub

Take care

Smallman