Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2007 Conditional Formatting Slow Performance Issue | Excel Worksheet Functions | |||
Slow Excel Navigation with Up / Down Arrow and slow scrolling | Excel Discussion (Misc queries) | |||
VBA Page Setup/Formatting EXTREMELY Slow... Help! | Excel Programming | |||
MS Excel is too slow when formatting, setuping page, etc. | Excel Discussion (Misc queries) | |||
VBA Slow Peformance on Formatting | Excel Programming |