Individually formatting large numbers of cells can be slow.

It looks like you are formatting blocks of 3 rows x X-cols at a time. What
you could do is format the first block, then pastespecial formats to every
4th cell down the first column. Also you could union a handful of '4th
cells' and paste special formats to the unioned range, union some more.
Start by clearing formats. Similarly you could adjust your 'every 4th row'
row width's to several unioned rows at a time.

Don't try union'ing large numbers of discontiguous ranges, that would be
couter-productive.

Difficult to read where your values come from, it might be possible to
assign values to a single array and apply all in one go.

Regards,
Peter T


"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