Prev: Changing the range for averages with out changing the formula.
Next: IF and Right Functions
From: Mike H on 20 Apr 2010 13:04
Hiding columns does not trigger calculation but if you put

application.volatile

at the start of the UDF it will recalculate when Excel does
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"RonaldoOneNil" wrote:

> I tried something similar and it almost works. I have my spreadsheet set to
> automatic calculation but it does not recalculate when I hide or unhide my
> column. Even if I press F9, it does not recalculate. I have to go into the
> cell with the UDF formula and click the tick on the formula bar before it
> recalculates.
>
> "Mike H" wrote:
>
> > Hi,
> >
> > Unfortunately the ideal solution SUBTOTAL only works on columns and not rows
> > so how about a User Defined Function.
> >
> > Alt+F11 to open VB Editor. Right click 'ThisWorkbook' and 'Insert module'
> > and paste this code in
> >
> > Call with this formula on the worksheet
> >
> > =sumvis(A1:C1)
> >
> >
> >
> > Function sumvis(rng As Range)
> > For Each c In rng
> > If c.ColumnWidth > 0 Then
> > If IsNumeric(c) Then
> > sumvis = sumvis + c.Value
> > End If
> > End If
> > Next
> > End Function
> > --
> > Mike
> >
> > When competing hypotheses are otherwise equal, adopt the hypothesis that
> > introduces the fewest assumptions while still sufficiently answering the
> > question.
> >
> >
> > "Brian" wrote:
> >
> > > if column a has the number 1, column b has the number 1 and column c has the
> > > number 1, column d has the function sum(A1:C1) for a total of 3. how can a
> > > change the formula so that I get an answer of 2 when column b is hidden.
> > >
> > >
First  |  Prev  | 
Pages: 1 2
Prev: Changing the range for averages with out changing the formula.
Next: IF and Right Functions