Cell formatting -- why so slow?
Yup, tried that with no luck...but a good thought and thank you. I'll see if
I can implement the suggestion from Peter in the next post.
Thanks again,
Jay
"RB Smissaert" wrote:
Have you tried with Application.Screenupdating = False at the start
and Application.Screenupdating = True at the end?
RBS
"Jay" wrote in message
...
Please take a look at the loop I'm running below...this is taking forever
to
execute. It looks pretty simple to me and I'm not sure what the hold up
is.
If I cycle through the code in debug mode, every "end with" takes some
time,
I assume because the formatting is taking so much time. Does anyone know
why
these formatting commands are taking so long to execute?
ws is a worksheet object.
colL colM and colN are collection objects with information pulled from a
table in another application.
For I = 1 To colM.Count
ws.Cells(((I - 1) * 4) + nStartRow, 1) = colL.Item(I)
With ws.Range(Cells(((I - 1) * 4) + nStartRow, 1), Cells(((I - 1) * 4)
+
nStartRow + 2, 1))
.Merge
.WrapText = True
.VerticalAlignment = xlTop
End With
With ws.Cells(((I - 1) * 4) + nStartRow, 2)
.Value = colS.Item(I)
.Cells.NumberFormat = "0.0000"
.Cells.HorizontalAlignment = xlCenter
End With
With ws.Cells(((I - 1) * 4) + nStartRow, 3)
.Value = colE.Item(I)
.Cells.NumberFormat = "0.0000"
.Cells.HorizontalAlignment = xlCenter
End With
With ws.Cells(((I - 1) * 4) + nStartRow, 4)
.Value = colM.Item(I)(nSegs)
.Cells.NumberFormat = "0.00"
.Cells.HorizontalAlignment = xlCenter
End With
With ws.Cells(((I - 1) * 4) + nStartRow + 1, 4)
.Value = colN.Item(I)(nSegs)
.Cells.NumberFormat = "0"
.Cells.HorizontalAlignment = xlCenter
End With
For J = 1 To nSegs - 1
With ws.Cells(((I - 1) * 4) + nStartRow, J + 4)
.Value = colM.Item(I)(J)
.Cells.NumberFormat = "0.00"
.Cells.HorizontalAlignment = xlCenter
End With
With ws.Cells(((I - 1) * 4) + nStartRow + 1, J + 4)
.Value = colN.Item(I)(J)
.Cells.NumberFormat = "0"
.Cells.HorizontalAlignment = xlCenter
End With
With ws.Cells(((I - 1) * 4) + nStartRow + 2, J + 4)
.Value = colLMH(I)(J)
.Cells.HorizontalAlignment = xlCenter
End With
Next J
'shrink every fourth row
ws.Cells(((I - 1) * 4) + nStartRow + 3, 1).RowHeight = 6
Next I
|