deleting NA

[John2007]

I need to have excel run a macro that will delete the entire row if there is a text "N/A" in a any cells that are in columns C through O. I know that this can be done via a loop...but is there an efficient way of doing this? The data set is 2000 rows

thanks,

j

[Richard Buttrey]

Hi,

You don't really need a macro. Just use a helper column and in say A1 use

Please Login or Register  to view this content.

Copy this down all 2000 rows and filter on column A for the value 1. Then select all these rows and delete them.

Of course a macro could perform exactly the same thing but if this is a one off exercise it's quicker just to do it.

HTH

[John2007]

thank you!!

john

[Palmetto]

Agree with Richard, but as I was working on the code I'll post what I have.

Please Login or Register  to view this content.

[John2007]

thanks for the counif idea. it works great. the countif function is in column AB. Is there a simple vba macro that would delete the entire row if there is a value of 1 in the Column AB? the reason i need the macro is b/c i have to apply this to many sheets.



j