Delete Rows if a Cell is Blank?

[pugsly8422]

I have about 10 files that are each nearly 5,000 rows. I need to delete each row that totals to 0. Instead of going through each file and delete the rows as I scroll down, I am hoping there's another way to do it.

I've attached a file. The last row (row D) has quite a few cells that are blank. I need to delete all of the rows that have blank cells in row D, but leave the ones that have values in row D, such as D94. Is there any way to do this withough deleting them as I go?

Thanks!

[arlu1201]

What about rows that have "-" in them?

[pugsly8422]

Sorry, yes, I would like to delete those too.

Thanks

[arlu1201]

You said you have 10 files which require this macro. Would you want this macro to open the file for you and then delete the rows? This way, you do not have to copy the same code to all the files but can use 1 macro for all files. However, you need to ensure that the sheet names are the same for the macro to work. Or you could even ask the macro for the sheetname.

[pugsly8422]

Wow. Actually, the sheet names are the same for each file, so that may work quite well.

[pugsly8422]

One thing I failed to mention, is that, if possible, I would like it to only check certain cells (which should be the same on each file). Looking at the attached file, it would be D9:D3435 and D3439:4875.

Thanks!

[pugsly8422]

Any possibility of this being solved? I really appreciate your effort!

[rscsmith]

Hi there

I'm most of the way there with this, but I have run out of time. Some points:
1) SUMPRODUCT should be used, not SUM. Using SUM, you are forced to then add subtotals together. When subtotals are 0, and so deleted, the cell referencing it will return #REF. Subtotal avoids this error. I have substituted SUM with SUBTOTAL in the attached.
2) I haven't managed to get the totals at the bottom to work. The issue is similar to that above.

I hope this helps.
US1000 RS.xlsm
Cheers, Rob.

[pugsly8422]

Thank you rscsmith. I appreciate your trouble and can see what you are saying. That definitely seems to be the first step in solving this problem.

I was out of town last week, but am back now, so I'm still hoping the experts on here can help me with this problem.

Thanks again!

[pugsly8422]

Any chance this is still possible?

[arlu1201]

I tried this code on your file

Please Login or Register  to view this content.

But i found that its also causing #REF errors. How do you want to handle that? Do you want to delete rows that are blank but do not have formulae?