Macro - Clear Contents of Cells With Certain Value

[Neutralizer]

Hi,

I'm trying to validate the data in a large spreadsheet (~12k rows and 30 cols) using the 'Circle Invalid Data'.

The problem I'm having is that some cells that appear blank (including when using Chip's CellView Addin) are apparently not completely empty and are thus getting circled rather than the data errors that I'm trying to weed out. Selecting the 'blank' cells that are invalid and using 'clear contents' solves the problem, the thing is there are 1000's of these cells scattered randomly throughout the data. Not all the cells that appear blank are throwing the error, so I'm assuming the ones that are throwing the error must contain a null string or something.

Does anyone know of a macro or a method that will basically allow me to search a given range of cells, looking for any cell with an empty string in it, and then applying the clear contents command to those cells?

 

[Neutralizer]

Nevermind, a simple two step find and replace procedure worked:

1. Ctrl+H -> Find: {Left it Blank} & Replace: =NA()
2. Ctrl+H -> Find: =NA() & Replace: {Left it Blank}

In step 2 I used 'match entire cell contents' as an option since NA() was also used as a component of other formulas.

Cheers