Delete entire row based on multiple cell values

[The Phil]

Hi,

I want to delete entire rows and shift the rows up if column I and J are BOTH equal to zero. It's that simple!

Since this is in the middle of a larger macro, it might be safe to specify that it's for sheet 'Final Import'.

Could anyone help me with this?

Thanks!!

[hectop]

May not be most elegant way but this will work. You should be able to easily adapt it.

Please Login or Register  to view this content.

[stnkynts]

Another option

Please Login or Register  to view this content.

[The Phil]

Hmm, it seems to work, but I'm having some issues. I watched it start and select the first row where there were zero values, then a couple seconds later it deleted the row, then a couple seconds later it selected the next row that matched the criteria, then a couple seconds later it deleted it. It seems to be running super slow for some reason.

I added the following and tried again and it was still super slow.

Please Login or Register  to view this content.

Any ideas on why it would be running so slowly?

Thanks

[The Phil]

I tried reducing my data down to 52 rows and ran both of your scripts. They work, but it takes them each a couple of minutes to delete 5 out of 52 rows. It seems that it should only take a couple of seconds.

If I step through the macros, it seems to hang on both when there is a delete command, so for some reason that command takes a few seconds to process instead of simply deleting the row in a fraction of a second.

I've been googling why this may be and can't find anything.

[stnkynts]

Hmmmmm. The solution I provided should be almost instantaneous. Submit your workbook.

[The Phil]

Hi,

Something else in my workbook must be causing it. I can't submit it because there is too much corporate data in it.

I found out that if I manually select a row and delete row, it also takes a couple seconds, so it's not the code. I have formulas set to manual, and there's nothing referring to that sheet, and that sheet doesn't refer to anything else. Something about deleting rows with or without a macro is running very slowly.

Thanks for the code though, it looks like it works great!!

[hectop]

Not sure why. I tried it on 200 rows and it was very quick.

[berlan]

Not considering autofilter, this should be a good alternative:

Please Login or Register  to view this content.

Regards,
berlan

[The Phil]

On a different sheet I had a huge block of complicated formulas. It was 4 columns and about 11,200 rows of formulas. I deleted them and everything worked very quickly.

I'm not sure why that would be though. None of them referred to the sheet that this macro was touching, and the sheet that this macro was touching doesn't refer to any other sheet. I had also set the recalculation to be manual.

Oh well, I'll have to work around it.

[stnkynts]

Did you properly turn off the calculations?

Please Login or Register  to view this content.

Note Application.Calculation = Manual is not correct. It will do nothing.