Bernie,

TO test this out before making changes to App, I tried this:
D12= IF(D12<"",AVERAGE(A12:C12),"")
However this is leading to circular reference problem. I might have
misunderstand what you are trying to suggest by saying:
You have
=Formula

Change to
=IF(B6<"",Formula,"")


Reason I am doing all this is becuase if I don't clear the cell my Median
formula takes the content (0s) of non populated columns as well. Is there
way I can avoid including the hidden column in the formula? So lets say in
above example
D12 = Average(A12:C12). If I hide Column C with 0 content (defualt value I
am suing) it will still be included in the Average calculation, which is not
what I want. However if I Clear column C, my average will only include A and
B column.

Please clarify. Thanks
-Sharad

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sharad ,

Use this to clear numbers:
Range.SpecialCells(xlCellTypeConstants, xlNumbers).Clear();

or this to clear text:
Range.SpecialCells(xlCellTypeConstants, xlTextValues).Clear();

or this to clear numbers and text:
Range.SpecialCells(xlCellTypeConstants,xlNumbers + xlTextValues).Clear();


From help:

expression.SpecialCells(Type, Value)
expression Required. An expression that returns one of the objects in
the Applies To list.
Type Required
XlCellType. The cells to include.
XlCellType can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

Value Optional Variant. If Type is either xlCellTypeConstants or
xlCellTypeFormulas, this argument is used to determine which types of
cells to include in the result. These values can be added together to
return more than one type. The default is to select all constants or
formulas, no matter what the type. Can be one of the following
XlSpecialCellsValue constants:
XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

HTH,
Bernie
MS Excel MVP


"Sharad Vyas" wrote in message
...
Great.While trying this
Range.SpecialCells(xlCellTypeConstants).Clear();
Range.SpecialCells has two parameter second one is value of type object,
what should i use for that?


"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Sharad,

Write your formulas to return "" if their input cell is blank:

You have
=Formula

Change to
=IF(B6<"",Formula,"")

and then don't delete your formulas, perhaps by changing

Range.Clear()

to

Range.SpecialCells(xlCellTypeConstants).Clear()

HTH,
Bernie
MS Excel MVP


"Sharad Vyas" wrote in message
...
I am working with VSTO with C#. My worksheet has 10 Columns and many
nows. Some of the cell contains formula and other are populated with
data from the data source.I define formating, formulas and fill in all
cells with 0s (where there is no formula). I have to compute Meaid
across the column for each row. When I load the data, it dictates
whether I will have 2 or 4 or all 10 columns filled in. Once I load the
data (lets say 4 columns), programatically I clear the remaining 6
columns. All works fine until this point. If I change some parameter and
reload the data in the same session, and lets say it has (5 columns)
more than 4 columns this time, all the cells , which had formula, for
5th columns, remains null. Data load is just fine. The reason for this
issue is that when I cleared the 6 columns during my first data loas, it
cleared my formula too.

My quesitons is how can I maitain/keep the formula, which I set in the
Excel, whiling Clearing the cell programtically. As I said I am using
C# with VSTO and using Excel object model;s Range.Clear() method to
clear the cell.

Help greatly appreiciated!!
-Sharad