Clear Empty Cells from Data

rwmill9716

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

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,388
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
434
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,388
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,388
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,363
Members
414,139
Latest member
okela0

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