I've got a For loop cycling through all the selected cells and
manually calculating them. However, it always calculates them by row
and it calculates much faster if going down the way but I don't want
to select each column and run my macro individually. Any ideas for
making the for loop move to the next cell down as it's looping through
the selected cells?

Sub CalcRange()
Dim nocells, currcell As Double
Dim Cell As Object

ActiveSheet.Activate

nocells = Selection.Cells.count
currcell = 0

For Each Cell In Selection
currcell = currcell + 1
Application.StatusBar = Int(currcell * 100 / nocells) & "%
complete"
Cell.Calculate
Next Cell
Application.StatusBar = False
End Sub

Generally a for each loop as you have is the most efficient. However I've
also noticed in some scenarios it's faster to loop by columns.

Following is untested aircode, just for ideas

Dim rng as Range, rC as range, rR as range, cell as range
dim i as long, nRwsCnt as long

Set rng = Selection
For each rC in rng.Columns
For each cell in rC.Cells
cell.calculate

occasionally by index can be faster

nRwsCnt = rng.Rows.Count
For each rC in rng.Columns
for i = 1 to nRwsCnt
rC(i).calculate

Chances are updating statusbar in iach loop takes up more time than the
actual process. For acuracy of about 1/18 sec's compare times with

dim t as double
t = Timer
' do stuff
t = Timer - t
debug.? t

Curiosity, why calculate every cell in a loop rather than say the worksheet

Regards,
Peter T

"Erasmus" <(E-Mail Removed)> wrote in message
news:465e110e-645a-4376-abab-(E-Mail Removed)...
> I've got a For loop cycling through all the selected cells and
> manually calculating them. However, it always calculates them by row
> and it calculates much faster if going down the way but I don't want
> to select each column and run my macro individually. Any ideas for
> making the for loop move to the next cell down as it's looping through
> the selected cells?
>
> Sub CalcRange()
> Dim nocells, currcell As Double
> Dim Cell As Object
>
> ActiveSheet.Activate
>
> nocells = Selection.Cells.count
> currcell = 0
>
> For Each Cell In Selection
> currcell = currcell + 1
> Application.StatusBar = Int(currcell * 100 / nocells) & "%
> complete"
> Cell.Calculate
> Next Cell
> Application.StatusBar = False
> End Sub

On Jul 29, 12:18*pm, "Peter T" <peter-t@discussions> wrote:
> Generally a for each loop as you have is the most efficient. However I've
> also noticed in some scenarios it's faster to loop by columns.
>
> Following is untested aircode, just for ideas
>
> Dim rng as Range, rC as range, rR as range, cell as range
> dim i as long, nRwsCnt as long
>
> Set rng = Selection
> For each rC in rng.Columns
> For each cell in rC.Cells
> cell.calculate
>
> occasionally by index can be faster
>
> nRwsCnt = rng.Rows.Count
> For each rC in rng.Columns
> for i = 1 to nRwsCnt
> rC(i).calculate
>
> Chances are updating statusbar in iach loop takes up more time than the
> actual process. *For acuracy of about 1/18 sec's compare times with
>
> dim t as double
> t = Timer
> ' do stuff
> t = Timer - t
> debug.? t
>
> Curiosity, why calculate every cell in a loop rather than say the worksheet
>
> Regards,
> Peter T
>
> "Erasmus" <JEEras...@googlemail.com> wrote in message
>
> news:465e110e-645a-4376-abab-(E-Mail Removed)...
>
>
>
> > I've got a For loop cycling through all the selected cells and
> > manually calculating them. However, it always calculates them by row
> > and it calculates much faster if going down the way but I don't want
> > to select each column and run my macro individually. Any ideas for
> > making the for loop move to the next cell down as it's looping through
> > the selected cells?
>
> > Sub CalcRange()
> > * *Dim nocells, currcell As Double
> > * *Dim Cell As Object
>
> > * *ActiveSheet.Activate
>
> > * *nocells = Selection.Cells.count
> > * *currcell = 0
>
> > * *For Each Cell In Selection
> > * * * *currcell = currcell + 1
> > * * * *Application.StatusBar = Int(currcell * 100 / nocells) & "%
> > complete"
> > * * * *Cell.Calculate
> > * *Next Cell
> > * *Application.StatusBar = False
> > End Sub- Hide quoted text -
>
> - Show quoted text -

I'm pulling in results from stochastic runs which are stored in large
dbf files. It pulls in each column(variable) seperately over time
periods going down for 40 to 50 years (at each month this is up to 600
rows worth). The way it works when you open the dbf file, you're much
faster pulling in the time periods for one variable in one file than
flipping between a few files, getting all variables at time 0 and then
moving on to time 1.
If I can set it to calculate the column, it should speed up
calculations from 20-30 minutes down to within 5.

I'm off to lunch but will try this after - thanks for that, does look
like it should work.

Peter,

Thanks, got that working fine - although where does the debug thing
show up? I use progress bar to get a rough idea as these sheets take
so long to calculate anyway. If we don't know where they're at, half
the time we crash the macro (or completely shut excel if it's just
calculating the whole sheet).

In answer to your question tho - there may be sections that we want to
pull through without having to wait ages for the rest of the sheet to
update. As an idea, the sheet I'm currently pulling in (one of a few
in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary
files. And the calculations are pretty slow as it's opening dbf files
to find the results we need.

"Erasmus" <(E-Mail Removed)> wrote in message
> Thanks, got that working fine - although where does the debug thing
> show up?

On the VBE's main menu, View, Immediate Window, or simply Ctrl-g

> I use progress bar to get a rough idea as these sheets take
> so long to calculate anyway.

Fair enough

> If we don't know where they're at, half
> the time we crash the macro (or completely shut excel if it's just
> calculating the whole sheet).

That can't be right at all

> In answer to your question tho - there may be sections that we want to
> pull through without having to wait ages for the rest of the sheet to
> update. As an idea, the sheet I'm currently pulling in (one of a few
> in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary
> files. And the calculations are pretty slow as it's opening dbf files
> to find the results we need.

Wouldn't Automatic calculation work OK for your needs.

Regards,
Peter T

On Jul 29, 2:59*pm, "Peter T" <peter-t@discussions> wrote:
> "Erasmus" <JEEras...@googlemail.com> wrote in message
> > Thanks, got that working fine - although where does the debug thing
> > show up?
>
> On the VBE's main menu, View, Immediate Window, or simply Ctrl-g
>
> > I use progress bar to get a rough idea as these sheets take
> > so long to calculate anyway.
>
> Fair enough
>
> > If we don't know where they're at, half
> > the time we crash the macro (or completely shut excel if it's just
> > calculating the whole sheet).
>
> That can't be right at all
>
> > In answer to your question tho - there may be sections that we want to
> > pull through without having to wait ages for the rest of the sheet to
> > update. As an idea, the sheet I'm currently pulling in (one of a few
> > in this wb & many wb's) has 31,325 numbers to pull in from 4 temporary
> > files. And the calculations are pretty slow as it's opening dbf files
> > to find the results we need.
>
> Wouldn't Automatic calculation work OK for your needs.
>
> Regards,
> Peter T

Sadly automatic calculation would render our remote / desktop pc's
useless for about an hour after opening any spreadsheet. It's just a
lot of data - pulled in using an add-in (effectively just a macro
spreadsheet) provided by another company and each cell is just really
slow pulling in results from database files and calculating them. To
be fair tho, the smallest database file we use for results is around
100MB, the largest somewhere between 2 and 3 GB.