EXCEL VBA deleting empty rows code with formulas

Hi

I have this really simple code...

Please Login or Register  to view this content.

Unfortunately the range it is looking at is full of formulas and therefore the code isnt working properly.

What am i doing wrong??

I know there's an easy answer for this ans i'm going to kick myself when i find out!!

Thanks

If you are wanna delete row a having formulas then use following code..

Dim i As Integer

Please Login or Register  to view this content.

you can also use OR instead of and

Don't forget to click *

If you don't and to delete use..

Please Login or Register  to view this content.

Don't forget to click *

I didnt explain it very well...

I'm trying to delete the rows that DO NOT have a "1" in it.

however all the rows create the "1" by formula.

I'm trying to look at the VALUE rather than the CELL CONTENTS but i'm not sure how to do it!?

Pl send sample of your file.

Super,
It is easier and faster to use a filter than a loop, but if you want to delete a row, you need to loop back ward, not forward.

Please Login or Register  to view this content.

Try this...

Please Login or Register  to view this content.

Don't forget to click *

None of the above worked...sorry

but i've figured out whats happening - because the loop goes through each line, its missing every other line out that equals the required value!!!

hence why its not deleting all the lines i need.

is there a way round this?

thanks

It is also possible that the value you try to exclude is 1, not "1".
If this does not work, please attach a sample and will be solved in no time.

Please Login or Register  to view this content.

Super,
You have formula in sheet1, in column A. What exactly do you want to do? Do you want to delete empty rows, or values which are not 1? Please explain.

This code deletes any cell with out error and if the cell value is not 1. It works.

Please Login or Register  to view this content.

The most efficient way is to use filter, but first you need to include if error in your fromula to remove the errors.
=IFERROR(IF(T211=0,0,1),0).
You do not even a code for this simple task.

AB33, I agree, but as long as you delete the rows at the same time, as in my routine, you won't get any errors in the first place.

Any where! If you have a formula and for what ever reason, the formula returns an error, you can replace the error with something, in this case with 0, but you can use any value instead of the error. It is an excel function post 2007.

Hi Berlan,
Yes, your code is much faster than a for each loop, but a filter is much faster as you do not need to loop at all. I have already pointed out this on post 5.

AB33, I can see that

For a filter routine, this code takes me 0.268 seconds (vs. 0.32 seconds for my other code in post #14):

Please Login or Register  to view this content.

Best,
berlan