cannot make the excel fill in/delete cells fast

[Johny B.]

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.

 

[Norman Jones]

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