cannot make the excel fill in/delete cells fast

Hi,
I have problems with filling in/deleting the cells in some 6 sheets workbook.
It should be a excel based tool for simple financial analysis.
There are P&L statement, Balance sheet, Cash-flow statement, Ratios, etc. in
respective worksheet.

There is a specific structure of input cells in each worksheet (about 1000
cells). In array PL, there are the numbers rows. In array NMonths, there are
number of columns.
--------------------------------------------------------------------------
Dim NMonths(1 To 24) As Integer....there are 24 collumns

PL(1) = 5
PL(2) = 6
PL(3) = 10
....................
...........-in array PL, there are numbers of rows

NMonths(1) = 6
NMonths(2) = 7
NMonths(3) = 8
....................
...........-in array NMonths, there are numbers of collumns


Than, in a loop, I am trying to fill in/delete the cells.

For col = 1 To 24
For ro = 1 To 45
Worksheets(2).Cells(PL(ro), NMonths(col)) = PLdata(col,ro)
Next ro
Next col

------------------------------------------------------------------
It works very slowly - mentioned loop lasts about 1 minute - on normally
very fast computers.

Are there any other faster ways of referencing cells or what would be the
most efficient solutions for of filling in/deleting this amount of not
neighbouring cells?

Thanks for your help!

Johny B.

Hi Johny B,

Aside from any consideration of your code, try a structure something like:

Sub AAA()
'Your Dim statements

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Your processing code

With Application
.Calculation = CalcMode
.ScreenUpdating = True
End With

End Sub

---
Regards,
Norman



"Johny B." <jan_niemiec@seznam.cz.(bez_spamu)> wrote in message
news:C2F09831-9E1B-40B7-9D49-E482A8A1B14D@microsoft.com...
> Hi,
> I have problems with filling in/deleting the cells in some 6 sheets
> workbook.
> It should be a excel based tool for simple financial analysis.
> There are P&L statement, Balance sheet, Cash-flow statement, Ratios, etc.
> in
> respective worksheet.
>
> There is a specific structure of input cells in each worksheet (about 1000
> cells). In array PL, there are the numbers rows. In array NMonths, there
> are
> number of columns.
> --------------------------------------------------------------------------
>As Integer....there are 24 collumns
>
> PL(1) = 5
> PL(2) = 6
> PL(3) = 10
> ...................
> ..........-in array PL, there are numbers of rows
>
> NMonths(1) = 6
> NMonths(2) = 7
> NMonths(3) = 8
> ...................
> ..........-in array NMonths, there are numbers of collumns
>
>
> Than, in a loop, I am trying to fill in/delete the cells.
>
> For col = 1 To 24
> For ro = 1 To 45
> Worksheets(2).Cells(PL(ro), NMonths(col)) = PLdata(col,ro)
> Next ro
> Next col
>
> ------------------------------------------------------------------
> It works very slowly - mentioned loop lasts about 1 minute - on normally
> very fast computers.
>
> Are there any other faster ways of referencing cells or what would be the
> most efficient solutions for of filling in/deleting this amount of not
> neighbouring cells?
>
> Thanks for your help!
>
> Johny B.