Excel 2007 : missing values

[sadafh]

Hi,

I was a large dataset with multiple attributes. Can anyone please tell me how can I remove records with any missing value for any of its attributes. The no. of records is too high to go through each and every record and look for the missing value and then delete that record. Hope there is a work around. Thanks.

[TMS]

You don't offer much information, so I will make some assumptions:

1. there is a header in row 1
2. the data, therefore, starts in row 2
3. you have a key field in column 1
4. the attributes start in column B and run to, say, column M
5. there is no more data beyond these attributes

If any of these assumptions is wron, you will need to make adjustments

You can add a Helper column at the end of the attributes, in this case in column N

In cell N2, put the formula: =COUNTIF(B2:M2,"") and drag down to the end of the data.

You can now use Autofilter and filter on column N for non-zero values.
Any row displayed will have at least one missing attribute.
Select all the rows and delete them.
Turn off the Autofilter
Delete the Helper column

Done!

Regards

[TMS]

For more assistance, please upload a sample workbook.

Regards

[sadafh]

Thank you TMShucks, I was able to follow your instructions and it worked.