I have faced very strange problem with the following code:

Type C_data
D As Date
T As Double
A As String
B As String
R As Byte
C As String
PR As Double
P As Byte
End Type

Public Carray(150000) As C_data
---------------------------------------------
Count=1
Application.ScreenUpdating = False
For i = StartPos To Cnt
Cells(Count, 1).Value = Carray(i).D
Cells(Count, 2).Value = Carray(i).T
Cells(Count, 3).Value = Carray(i).A
Cells(Count, 4).Value = Carray(i).B
Cells(Count, 5).Value = Carray(i).R
Cells(Count, 6).Value = Carray(i).C
Count = Count + 1
Next i
Application.ScreenUpdating = True
---------------------------------------------

This code works ok with a workbook containing one worksheet. It takes
about 15 secs for 50000 records. But on my actual workbook I have
several sheets and same code for the same 50000 records is executed for
more than 3 minutes. I have figured out that this is influenced by two
sheets containing about 6000 cells with comments. I have removed all
comments and the problem has gone. But I need those sheets with
comments :(
Does anyone has any ideas why it happens?


--
side_
------------------------------------------------------------------------
side_'s Profile: http://www.excelforum.com/member.php...o&userid=19458
View this thread: http://www.excelforum.com/showthread...hreadid=478244

Try turning the calculations off.
Application.calculation = xlmanual
application.calculation = xlAutomatic

Once your spreadsheet gets beyond 65,536 dependencies in the formulas it
looses the ability to perform smart calculations and it recalculates all
every time. This might be your problem.
--
HTH...

Jim Thomlinson


"side_" wrote:


I have faced very strange problem with the following code:

Type C_data
D As Date
T As Double
A As String
B As String
R As Byte
C As String
PR As Double
P As Byte
End Type

Public Carray(150000) As C_data
---------------------------------------------
Count=1
Application.ScreenUpdating = False
For i = StartPos To Cnt
Cells(Count, 1).Value = Carray(i).D
Cells(Count, 2).Value = Carray(i).T
Cells(Count, 3).Value = Carray(i).A
Cells(Count, 4).Value = Carray(i).B
Cells(Count, 5).Value = Carray(i).R
Cells(Count, 6).Value = Carray(i).C
Count = Count + 1
Next i
Application.ScreenUpdating = True
---------------------------------------------

This code works ok with a workbook containing one worksheet. It takes
about 15 secs for 50000 records. But on my actual workbook I have
several sheets and same code for the same 50000 records is executed for
more than 3 minutes. I have figured out that this is influenced by two
sheets containing about 6000 cells with comments. I have removed all
comments and the problem has gone. But I need those sheets with
comments :(
Does anyone has any ideas why it happens?


--
side_
------------------------------------------------------------------------
side_'s Profile: http://www.excelforum.com/member.php...o&userid=19458
View this thread: http://www.excelforum.com/showthread...hreadid=478244

I have tried Application.Calculation = xlManual and eve
Application.EnableEvents = False , but nothing changed. The only thin
that helped was removing comments in two other sheets of sam
workbook.
I suppose this could be some kind of memory problem, but I am not sure
Other strange thing is xls file size, which increases by several MB whe
comments are added. There is not so much text in comments but probabl
they are stored with extensive format data

--
side
-----------------------------------------------------------------------
side_'s Profile: http://www.excelforum.com/member.php...fo&userid=1945
View this thread: http://www.excelforum.com/showthread.php?threadid=47824