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" <(E-Mail Removed)> wrote in message
> news:8964454A-0B6A-4865-AEA7-(E-Mail Removed)...
> > 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
>
>
>
|
Similar Threads
