CODEWITHSUNDEEP
vbaexcel
Dino C
Dino C

Reputation: 317

Faster row delelete method

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

Upvotes: 1

Views: 82

Answers (2)

Dino C
Dino C

Reputation: 317

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

Upvotes: 0

ASH
ASH

Reputation: 20302

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

Upvotes: 1

Related Questions