VBA delete rows

[gd6noob]

I using the Autofilter method and I would like to change to using the loop function, as it might be faster? I have anywhere from 25,000-30,000 rows..

Trying to get Delete rows if the cell in columns C contains "Pending"

My code kinda works, but it misses a few...

Sub LoopCells()

Dim cell As Range, Pending As Range
Set Pending = Range("C2:C" & Cells(Rows.Count, "C").End(xlDown).Row)

For Each cell In Pending
    If cell.Value = "Pending" Then
        cell.EntireRow.Delete
    End If
Next cell

End Sub

Another request, if possible, can I modified this to if the cell contains a date?

 

[Michael M]

@Peter_SSs
Should the code I provided be sufficient, without the need for a helper column ??

 

[Peter_SSs]

@Peter_SSs
Should the code I provided be sufficient, without the need for a helper column ??

Hi Michael

Your code in deleting the relevant rows directly from their existing positions is similar to the OP's original method of 'Autofilter and delete' in that if there is a lot of disjoint row ranges to delete the deletion can be quite slow. The OP was trying to change from that method in a hope of increasing speed.

The exact result depends on what is in the sheet besides what is in column C & what is "sufficient" but note my answer to your question about using a helper column was about speed, not sufficiency.

The OP say there is 25,000 to 30,000 rows. If there are, say, about 10% of the rows "Pending" and they are scattered throughout those rows, your code could be quite slow, particularly if there are any formulas in the other columns. For example, I set up a sheet with 30,000 rows where column C was populated with digits 1 to 9 randomly then "1" was replaced with "Pending" & very simple formulas placed in columns A & B like this.

gd6noob.xlsm
	A	B	C
1
2	11	13	8
3	9	11	6
4	7	9	4
5	10	12	7
6	8	10	5
7	10	12	7
8	12	14	9
9	6	8	3
10	#VALUE!	#VALUE!	Pending
11	8	10	5
12	10	12	7
13	8	10	5
14	5	7	2
15	#VALUE!	#VALUE!	Pending
16	11	13	8
Sheet2

Cell Formulas
Range	Formula
A2:A16	A2	=C2+3
B2:B16	B2	=C2+5

Your code took 28+ seconds to delete the relevant rows & on the identical data mine took 0.42 seconds.
Now if those values in columns A:B were also constants rather than formula results, your code 'only' took 1.8 seconds but that is still relatively slow compared to the 0.3 seconds for my code.

 

[Michael M]

Peter
Thank you for the comments and insights. ....always learning...

 

[Marc L]

For big / huge data, the more non contiguous rows to delete, the slower​

and as clearing is faster than deleting the sort on a helper column - if necessary as it could be a column from the data range -​

then clearing at once the block of rows to be 'deleted' may be faster than others ways …​

​

Rank methods from slower to faster :​

#4 : deleting row by row​

#3 : AutoFilter & Delete (if the range is not an Excel table so SpecialCells is useless)​

#2 or 1 depending on the worksheet design and the number of rows : sort & clearing …​

Just try !​

​

If there are not much non contiguous rows to delete so the Autofilter & Delete method is fast enough …​

 

[gd6noob]

Thank you @Peter_SSs , @Marc L and @Michael M.

@Peter_SSs, this works very fast, even tried to to go up to 115k rows and it still fast.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.