Macro to remove rows with zero values in a column

Hi All,

Looking for some quick running code to remove rows containing a zero value in column Z.

Row 1 contains column headings and Row 2 downwards contains the data.

Please Login or Register  to view this content.

Originally Posted by AB33

Please Login or Register  to view this content.

wow that was fast. I get a debug error Runtime error 1004 no cells found on this bit of code

Please Login or Register  to view this content.

Not sure if it makes a difference but column Z is a formula

Please Login or Register  to view this content.

The code does not find empty cells. You can trap the error, but the code might not delete the rows.

Please Login or Register  to view this content.

Originally Posted by OllyXLS

Please Login or Register  to view this content.

Thanks for this. Looks like it is working but had to stop it as it was taking a long time to run.

I've turned screen updating and auto calculate off but seems to be running incredibly slowly. Any tips to speed it up? Applying it to about 5000 rows of data

Originally Posted by AB33

The code does not find empty cells. You can trap the error, but the code might not delete the rows.

Please Login or Register  to view this content.

it runs through now but doesn't delete any rows

Yes, because you have got empty string, but not empty cells. Did you try the filter?

Last try

Please Login or Register  to view this content.

You could try this, might be a bit quicker:

Please Login or Register  to view this content.

How long does your sheet take to recalculate? I tested it on 7000+ rows and it was a few seconds.

what value do you get for ActiveSheet.UsedRange.Rows.count?

5890 rows at the moment, takes +- 2 seconds to fully recalculate the workbook

How long does it take just for this:

Please Login or Register  to view this content.

?

4-5 seconds

So it must be this line:

Please Login or Register  to view this content.

that is taking time.

I don't see what would be quicker than selecting all the rows and deleting them in one go, apart from turning off calculation as you mention I don't think you can speed it up much more. What is the code you are using to turn the calculation off?

I just tried filtering and selecting zero value rows only then manually deleting with manual calculation on and that takes +- 3 min.

I added this extra to your code.

Please Login or Register  to view this content.

Just thinking out loud but is this any quicker:

Please Login or Register  to view this content.

thanks for all the help peeps! AB33 that code works well

AB33's code #19 looks identical to the code in post #10 to me. Are you ssaying the code in #19 is faster than that in #10?

If so, anyone got any pointers as to why?

Did you try the code in post #18 to see if it ran quicker?

code in #19 isn't much faster if at all.

code #18 created another tab with unformatted data but didn't remove any rows from original data tab

Code#18 wasn't set up to solve the problem. I was wondering if it was faster to run. If so, it might be quicker to copy the rows you want into a new sheet, delete the original and rename the new sheet. Seeing as it was deleting the rows that seemed to be the problem.

Originally Posted by yudlugar

Code#18 wasn't set up to solve the problem. I was wondering if it was faster to run. If so, it might be quicker to copy the rows you want into a new sheet, delete the original and rename the new sheet. Seeing as it was deleting the rows that seemed to be the problem.

ah i understand now. Yes think it runs a bit faster but original data sheet has some formulas and formatting i'd like to keep so that way wouldn't really be worth it. I guess i'll just have to live with the running time, probably not the end of the world

Ah ok, the code I gave you will have copied the formulas but not formats you could try:

Please Login or Register  to view this content.

But that might take longer. How consistent are the formats? What do they look like, it shouldn't be too difficult to reapply them...

hmm formatting is a mixed bag of dates, values, text, etc. not too hard to replicate.

The quickest way to do it manually is sorting the value column by smallest to largest or largest to smallest and then manually deleting all the zero value rows. Might be tricky to automate this to find the range of all the zero value rows though

Is it consistent in each column?

Any chance you can upload a workbook? Just a few rows to demonstrate what the formatting looks like, the data itself can be replaced with non-sensitive info if neccessary.

What you mention as the quickest way manually is what the code in #10 and #19 does. I think it might be quicker this way though.

test file attached with formatting

Test.xlsx

please note there are about 30 other tabs which feed off this single tab which have been removed

Try. This is faster

double post oops

It is inconceivable a loop and filter could take the same amount of time. Yes, sorting can make a difference, but without it ,filter is still much faster than any loop.

I think the issue may be with the type of data you have rather codes. Are these data imported from a site, or Accounting applications, such as SAP? If so, then you need to clean these data first.

the data is an export from oracle into an excel file. I then cut and paste this data to a model and copy format/formula's down from rows above.

Ohm! I knew that.

Data imported from Oracle have lots of unseen characters. Yes, copying and pasting VALUES ONLY will get rid most of these dirty data, but not all. What you need is to trim and clean data as well. You should not also put the imported data with the clean data. Clean the data first, then delete that sheet and save the new one on its own. I have almost lost my laptop on 3 occasions because of this issue.
I have amended the code with sort code.

BOOM! AB33 took +- 1 second to run and works perfectly thanks for all the help really appreciate it!