Faster row delelete method

Question

There are 15 csv files. Each file has 8000 rows. In all files, column A contains either numerics or "Delete". Rows with the value "Delete" must be deleted. The following is more than slow. It's meaningless. Any other Suggestion?

 For j = lastRow To 1 Step -1
     If Wb1.Worksheets(1).Cells(j, 1).Value = "Delete" Then
        Wb1.Worksheets(1).Rows(j).Delete
        lastRow = Wb1.Worksheets(1).Cells(Cells.Rows.Count, "A").End(xlUp).Row
    End If
 Next j

This can help you: https://stackoverflow.com/a/17637270/5722505 — Egan Wolf, Feb 02 '18 at 08:41
How about sorting on column A first and then deleting the block of rows that are `delete`? — Alex P, Feb 02 '18 at 08:47
if rows are sorted, so 'delete' rows are at the bottom of your data you can clear them instead of deleting. This may speed things up. — TomJohn, Feb 02 '18 at 08:56
Try implementing deleting in on go either by [looping or filtering](https://stackoverflow.com/a/28685979/2685412) — L42, Feb 02 '18 at 09:22
Possible duplicate of [Excel VBA Performance - 1 million rows - Delete rows containing a value, in less than 1 min](https://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less) — L42, Feb 02 '18 at 09:27
Why not use pandas or R as your skill tags indicate? Each can easily import CSVs and delete rows! — Parfait, Feb 02 '18 at 22:30

score 1 · Answer 1 · answered Feb 02 '18 at 20:24

Try it like this.

ActiveSheet.AutoFilter.Range.Offset(1,0).Rows.SpecialCells(xlCellTypeVisible).Delete(xlShiftUp)

Or... Use SpecialCells to delete only the rows that are visible after autofiltering:

ActiveSheet.Range("$A$1:$I$" & lines).SpecialCells _
    (xlCellTypeVisible).EntireRow.Delete

score 0 · Accepted Answer · answered Feb 14 '18 at 15:20

This worked properly (5 mins for 15 csv of 9000 rows each)

For j = lastRow To 1 Step -1
        ValueToFind = "Delete" 'look at this value
        With Wb.Worksheets(1).Range("A:A") 'searches all of column A or whatever column
             Set Rng = .Find(What:=ValueToFind, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)

             If Not Rng Is Nothing Then
                GoTo Next_Row 'value found
             Else
                Wb.Worksheets(1).Rows(j).Delete       
             End If
        End With
Next_Row:     
Next j

Faster row delelete method

2 Answers2