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 |
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 |