Slow code

[nniedzielski]

I have this code that i am running that works, but this section is taking about 12 seconds to run, its deleting around 12k rows.

Is there a way to speed it up?

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim we as worksheet

' Filter column C for "Yes"
ws.Range("C1").AutoFilter Field:=3, Criteria1:="Yes"

' Delete filtered rows
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
ws.Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete

' Turn off filter
ws.AutoFilterMode = False

Application.ScreenUpdating = true
Application.DisplayAlerts = true
Application.EnableEvents = true

 

[Skyybot]

Try this. I made a small change to your code.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim we as worksheet
Dim myRange as Range

' Filter column C for "Yes"
ws.Range("C1").AutoFilter Field:=3, Criteria1:="Yes"

' Delete filtered rows
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set myRange = ws.Cells("C2:C" & lastRow)
myRange.Delete

' Turn off filter
ws.AutoFilterMode = False

Application.ScreenUpdating = true
Application.DisplayAlerts = true
Application.EnableEvents = true

 

[nniedzielski]

Try this. I made a small change to your code.

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

Dim we as worksheet
Dim myRange as Range

' Filter column C for "Yes"
ws.Range("C1").AutoFilter Field:=3, Criteria1:="Yes"

' Delete filtered rows
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set myRange = ws.Cells("C2:C" & lastRow)
myRange.Delete

' Turn off filter
ws.AutoFilterMode = False

Application.ScreenUpdating = true
Application.DisplayAlerts = true
Application.EnableEvents = true

it didnt work, when filered the next row after C1 was like C42, i think that stopped the code

 

[rlv01]

Try the easy thing first. Add this to the beginning

    Application.Calculation = xlCalculationManual

and this to the end

    Application.Calculation = xlCalculationAutomatic

 

[Skyybot]

Another one. You'll have to change sheet name possibly

Sub test()
Dim sht As Worksheet, wb As Workbook, myRange As Range

Set wb = ThisWorkbook
Set sht = wb.Sheets("Sheet1")
sht.Range("C1").AutoFilter Field:=3, Criteria1:="Yes"

Set myRange = Range(sht.Cells(2, 1), sht.Cells(sht.UsedRange.Rows.Count, sht.UsedRange.Columns.Count))

Application.DisplayAlerts = False

myRange.Delete

Application.DisplayAlerts = True

End Sub

 

[Michael M]

What about using....no filter

Sub DeleteRows()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Replace "Yes", "#N/A", xlWhole, , False, , False, False
   Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

 

[johnnyL]

What about a quick sort to delete all the rows to be deleted in one shot:

Sub TestRowDeletes()
'
    Dim ArrayRow                As Long, NumberOfRowsToDelete   As Long
    Dim NextEmptyColumnNumber   As Long
    Dim HelperColumnArray       As Variant, YesColumnArray      As Variant
    Dim ws                      As Worksheet
'
    Set ws = Sheets("Sheet1")                                                                                                               ' <--- Set this to the name of your sheet
'
    NextEmptyColumnNumber = ws.Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1  ' Get NextEmptyColumnNumber
'
    YesColumnArray = ws.Range("C1:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row)                                                         '
    ReDim HelperColumnArray(1 To UBound(YesColumnArray), 1 To 1)                                                                            '
'
    NumberOfRowsToDelete = 0                                                                                                                ' Reset NumberOfRowsToDelete
'
    For ArrayRow = 1 To UBound(YesColumnArray, 1)                                                                                           ' Loop thru rows of SumColumnArray
        If YesColumnArray(ArrayRow, 1) = "Yes" Then                                                                                         '   If the value is found then ...
            NumberOfRowsToDelete = NumberOfRowsToDelete + 1                                                                                 '       Increment NumberOfRowsToDelete
            HelperColumnArray(ArrayRow, 1) = 1                                                                                              '       Set row in HelperColumnArray = 1
        End If
    Next                                                                                                                                    ' Loop back
'
    If NumberOfRowsToDelete > 0 Then                                                                                                        ' If there are rows to be deleted then ...
        With ws.Range("A1").Resize(UBound(YesColumnArray), NextEmptyColumnNumber)                                                           '   Set range for possible deletion of rows
            .Columns(NextEmptyColumnNumber).Value = HelperColumnArray                                                                       '       Write the HelperColumnArray to the EmptyColumn
            .Sort Key1:=.Columns(NextEmptyColumnNumber), Order1:=xlAscending, Header:=xlNo                                                  '       Sort the Rows with '1's to the top
            .Resize(NumberOfRowsToDelete).EntireRow.Delete                                                                                  '       Delete the rows with '1's all at once
        End With
    End If
End Sub

 

[nniedzielski]

What about using....no filter

Sub DeleteRows()
  With Range("C1", Cells(Rows.Count, "C").End(xlUp))
    .Replace "Yes", "#N/A", xlWhole, , False, , False, False
   Columns("C").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
  End With
End Sub

this removed around 4 seconds from the code, so thank you!

 

[rlv01]

FWIW, a few years ago I did some testing for deletion speed using the filter approach. What I found that for data sets of ~25,000 rows or less the filter method was faster. Beyond 25K it was slower than other methods. I did some testing with your code and for me it will delete 12K rows in under one second. But that is for static data with no formulas or lookups.