Cell formatting -- why so slow?
 

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

Cell formatting -- why so slow?
 

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

Cell formatting -- why so slow?
 

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

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

Cell formatting -- why so slow?
 

Sounds like some good ideas...I'll give it a try. Thank you.

The numbers that go into the cells all come from two SPSS Pivot Table
objects (SPSS = statistical software package). I read them in from SPSS and
load the values into a series of collection objects for later use. Probably
not the most efficient way to transfer the data, but I'm pretty sure it's the
formatting that's taking the bulk of time to execute.

Thanks again for your help,

Jay

"Peter T" wrote:

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