Clear Empty Cells from Data

rwmill9716

Active Member
Joined
May 20, 2006
Messages
448
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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,894
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
May 20, 2006
Messages
448
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,894
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,894
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Gotta run, but assuming my understanding is correct (see post #4), change the range in this to suit your sheet.
Code:
Sub ClearBlankCells()
'Change range to suit
On Error Resume Next
Range("A5:M5").SpecialCells(xlCellTypeFormulas, 2).ClearContents
On Error GoTo 0
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,863
Messages
5,834,041
Members
430,257
Latest member
Todor T

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top