Delete entire row if the value is less than zero

[Niranjanrajrishi]

Hello,

I have a code to delete the Rows if the value in Column "W" is less than ZERO. I have more than 1 mil data the coding taking time. Is there anything wrong in the code? Is there a way to speed up the process?

 

[Chihiro]

You don't have any code in the sample you uploaded... hard to tell you what's wrong without it.

In most cases, deleting rows is most time consuming portion. I assume you loop through each row, check for condition, delete row if condition met, and then move onto next.

You may find Advanced Filter method faster. Or if you use auto-filter to show only rows that meet condition. And then delete visible range in one shot.

 

[vletm]

Niranjanrajrishi
as above ... it's challenge to comment Your code
but I would do it this way ... by pressing [ Do It ]

 

[Niranjanrajrishi]

Wow! Its pretty quick! Thank you so much! I tried many codes but none of them was fast as your code.

Can we do it without command button? I use this code with other coding and the process is huge. So if we can remove the message box, it would be good for me.
Thank you again for your great support.

 

[vletm]

Niranjanrajrishi
You can always delete that button and run marco as wanted.
I took away those messageboxes ...
so be careful and have backups before running it!

 

[Marc L]

Hi !

As clearing is faster than deleting (usefull for very big data !),
use a formula in a temp column returning FALSE for rows to keep
or TRUE for rows to delete, apply a sort on this temp column
on all the range then clear at end of table the block of TRUE rows
and delete content of temp column …
​

 

[Niranjanrajrishi]

Thank you so much! When I played around, it didn't work and your code helped me to identify the error I made.