I am working on single rows only so your solution does not give me a lot of
advange over Bernies. I will give yours a try though and see if I can find a
difference. When I tried it for 1000 iterations I got

Mine 2.1 Sec
Bernie 1.1 Sec

Thanks...

"toppers@johntopley.fsnet.co.uk" wrote:

> Jim,
>
> If you have a large number of (contiguous?) rows to calculate, the
> quickest method is to load the data into a variant (array) and then
> calculate. On a simulation of 1000 rows and all columns (up to column
> number 256) I got the following results:
>
> Your method ... approx 2800 miiliseconds (simply looped round 1000
> times)
> Bernie's method ... approx 1000 milliseconds (simply looped round 1000
> times)
> Using variant array .. approx 150 milliseconds
>
> The looping/calling function may account for some of the differences.
>
> Even with one row it will be quicker [but impossible to measure!]
>
> Sample code:
>
> Dim x as variant
>
> x = Range("D2:IV1000")
>
> For r = 1 To UBound(x, 1)
> For c = 1 To UBound(x, 2) Step 2
> dblReturnValue = dblReturnValue + x(r, c)
> Next c
> Next r
>
> HTH
>
>
> Jim Thomlinson wrote:
> > Thanks I will give that a try and see if there is much improvement.
> > Traversing is always slow so with any luck this will give me a
> boost...
> >
> > "Bernie Deitrick" wrote:
> >
> > > Jim,
> > >
> > > You can use a sumproduct formula to evaluate that directly through
> Excel,
> > > which should speed things up: (tested for all but speed ;-))
> > >
> > > x = Application.Evaluate("=SumProduct((mod(column(" & _
> > > Range(ActiveCell(1, 3), Cells(ActiveCell.Row, 256)).Address & _
> > > "),2)=" & ActiveCell.Column Mod 2 & ")*" & _
> > > Range(ActiveCell(1, 3), Cells(ActiveCell.Row, 256)).Address &
> ")")
> > > MsgBox x
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > > "Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in
> message
> > > news:5EEF37B4-80D4-4309-99FF-9493BFE8913E@microsoft.com...
> > > > I have a function that I need to run very frequently for an end
> user
> > > > application that is already slow enough. What I need to do is to
> add up
> > > the
> > > > values in every second column to the right of a given cell. I was
> hoping
> > > that
> > > > someone could look at this code and tell me if there is any way
> to squeek
> > > a
> > > > little more speed out of it...
> > > >
> > > > Option Explicit
> > > >
> > > > Sub test()
> > > > Dim x As Double
> > > >
> > > > x = AddAlternatingColumns(Sheet2, Sheet2.Range("B2"))
> > > > MsgBox x
> > > > End Sub
> > > >
> > > > Public Function AddAlternatingColumns(ByVal wks As Worksheet,
> ByVal
> > > > rngTarget As Range) As Double
> > > > Dim dblReturnValue As Double
> > > > Dim intLastColumn As Integer
> > > >
> > > > intLastColumn =
> > > wks.Range("A1").SpecialCells(xlCellTypeLastCell).Column
> > > > Do While rngTarget.Column < intLastColumn
> > > > Set rngTarget = rngTarget.Offset(0, 2)
> > > > dblReturnValue = dblReturnValue + rngTarget.Value
> > > > Loop
> > > > AddAlternatingColumns = dblReturnValue
> > > > End Function
> > > >
> > > > --
> > > > Thanks In Advance...
> > > >
> > > > Jim Thomlinson
> > >
> > >
> > >
>
>