Deleting Row based on number (big data)

[jack.a]

Hey all,

Looking for a way to delete rows based on a value "0" in column B

Here is my current code - it works really well, excpect for when I'm running 50,000 rows

Any thoughts on a code that wont take 20 minutes for that kind of size and therefore freeze my Excel while it does the work?

Please Login or Register  to view this content.

[davesexcel]

This takes about 7 seconds for 62k rows.

I replace all the 0's with blanks and then use specialCells to delete blank rows.

Please Login or Register  to view this content.

[jack.a]

Awesome! Great idea. Thank you

[vcoolio]

Hello Jack,

FWIW, another option would be to sort the data (sort on Column B for 0) and use the Autofilter:

This takes about half a second on 80K rows:-

Please Login or Register  to view this content.

Cheerio,
vcoolio.

[jolivanes]

Or

Please Login or Register  to view this content.

60,000 entries = 1/10 sec

Maybe I should read properly. Rows need deleting. Forget above suggestion.

[apo]

Hi jolivanes..

A bit rusty.. but maybe?

Tested with a contiguous data set with 7 columns.. change as needed.

Please Login or Register  to view this content.

[jolivanes]

Thanks apo. I'll play with it.
Much appreciated. I hope that jack.a does so also.

[jack.a]

Hey theree. Been off for the last few days...

so i retested this code

Please Login or Register  to view this content.

And it seems to remove all Zeroes for example it turns a cello that equals 2000 to 2, but it does delete the cells that equal zero.

Any thoughts?

[davesexcel]

Possibly we need to be more specific in the find/replace part.

Please Login or Register  to view this content.

[jack.a]

Getting an error on this

[davesexcel]

You need to be more specific.

Attach a sample Wb

[jack.a]

Ok attached.

[davesexcel]

Hi jack,
I don't get an error, what line errors for you?

Please Login or Register  to view this content.

[jack.a]

Please Login or Register  to view this content.

These lines get highlighted

[davesexcel]

I can't replicate the error.

The original code in Post#2 just replaced cells that =0 it did not replace all 0's in all cells, for me.

[jack.a]

It did for me originally as well, now it has decided to remove all "0"

[jack.a]

working now....must've been a weird computer glitch... THANKS ALL!!