Faster Code

[Satyaprakash]

Hello,

I have the following code which deletes the rows that contain a certain text. However given the enormous amount of data I deal with it takes a lot of time for the code to execute. Is there a faster way of doing this.

Sub DeleteRow()
Last = Cells(Rows.Count, "A").End(xlUp).Row
  For i = Last To 1 Step -1
  If (Cells(i, "A").Value) = "Parent Job I" Then
  'Cells(i, "A").EntireRow.ClearContents
  Cells(i, "A").EntireRow.Delete
  End If
  Next i
End Sub

 

[Jagdev Singh]

Hi Satyaprakash,

I would suggest the below VBA code to get your work done at much faster speed.

lastrow = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Range("A1").AutoFilter
    ActiveSheet.Range("$A$1:$L$1" & lastrow).AutoFilter Field:=1, Criteria1:="Parent Job I""
    Range("A2:L" & lastrow).SpecialCells(xlCellTypeVisible).EntireRow.Delete

I consider the column from A to L you can make the changes as per your need.

Regards,
JD

 

[Satyaprakash]

Hey Jaydev,

When I am using the code I am getting an "Application-defined or object-defined error" for the second line of the code

 

[p45cal]

given the enormous amount of data I deal with it takes a lot of time for the code to execute

Roughly how many rows and columns of data are we dealing with and roughly what proportion of cells contain formulae?
Oh, and how much time is 'a lot of time'?

 

[Jagdev Singh]

Hi

Please upload a sample file for more clarity on your need.

I Presume you defined the variable lastrow if not then define it

Dim lastrow as long

Regards
JD

 

[Marc L]

Hi !

Using a loop to delete rows is the slowest way for huge data !

A faster way in manual mode like by code :
• use an advanced filter to filter data on criteria to delete rows
• delete filtered rows …

Fastest way in manual mode like by code :
• sort data on key column
• filter data on criteria to delete rows
• delete filtered rows …
​

 

[Deepak]

Check it.

Sub DelRows()
Dim mycol As Integer, mystr As String

'where to filter column
mycol = 1

'what to filter
mystr = "Parent Job I"


With ActiveSheet.Range("A1").CurrentRegion
   
    .AutoFilter Field:=mycol, Criteria1:=mystr
   
  .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

End With

End Sub

 

[Marc L]

Just notice Copy & Delete methods treat only
visible cells of a range with hidden ones …

So in this case, SpecialCells is unnecessary !
​

 

[Deepak]

Just notice Copy & Delete methods treat only
visible cells of a range with hidden ones …

So in this case, SpecialCells is unnecessary !
​

Yes..

  .Offset(1).EntireRow.Delete

This will also do the same but i used as a precaution nothing else.

 

[Marc L]

Yes, for safety …

If prefer before doing a filter to check if there is a match
using COUNTA Excel function for example …
​