Clear Empty Cells from Data

[rwmill9716]

I have a large data set (65 columns by 500 rows), and several embedded cells are blank. I'm looking for pairwise correlations among the columns and have run into a problem. While function CORREL(array 1, array 2) works well, i.e., it doesn't use the blank cells, XY graphing the data is affected. Because of the blank cells, the graph plots one of the data columns against its row number rather than against the other column. If I manually do a "clear contents" on each of the blank cells, the plot works fine: plotting x against y leaving out all the blank cells.

I need a macro (or formula) that rewrites my data set and does a "clear contents" on each blank cell.

 

[JoeMo]

What do the "blank" cells have in them ... a formula or ??? In other words, if the cells appear to be blank, how would one know they contain something that can be cleared?

 

[rwmill9716]

Joe,

The cells were copied from a data set of formula in which only the values were copied. There is nothing in the cell, but the graphic program cannot use it unless I go in and clear contents for each blank cell. The correlation function, on the other hand, doesn't care.

I need a formula or macro that can be used to clear these blank cells.

Note, the original data set is being cleared of outliers, i.e., those data that are beyond +/ 3.5 standard deviations from the mean. The formula used looks like this for a data point in cell A10 (Outlier limits in cells A1 and A2):

=IF(OR(A10>=A1,A10<=A2),"",A10)

The blank cells that I'm having trouble with come from the "" treatment.

 

[JoeMo]

From your response I think the cells contain a formula that returns "". If that is correct what is the range you want to do the clearing in?

 

[JoeMo]

Gotta run, but assuming my understanding is correct (see post #4), change the range in this to suit your sheet.

Sub ClearBlankCells()
'Change range to suit
On Error Resume Next
Range("A5:M5").SpecialCells(xlCellTypeFormulas, 2).ClearContents
On Error GoTo 0
End Sub

 

[rwmill9716]

Thanks, Joe, that seems to work well.

ric