adding colors, patterns, comments to large spreadsheets in bulk
 

Never looked at vba/excel before, but was able to reduce run time on an app
by 75% via recorset streamlining.

Still not happy with run times, learned that manipulation of cells is
consuming most of the other 25%.

Learned that perhaps adding values in bulk to spreadsheet (rather than one
by one) would probably improve performance drastically.

So I'm wondering if the pretty substantial color, pattern and comment
manipulation we're also doing can somehow be applied in bulk, perhaps from an
array or similar workaround?

adding colors, patterns, comments to large spreadsheets in bulk
 

Have a look at this sample code from Dave Peterson.

Sub ColorFormulas2()
'work on an entire range without For Each.........Next
Dim rng As Range

Set rng = Nothing
On Error Resume Next 'just in case there are no formulas
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.Interior.Color = vbYellow
End If
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 Apr 2008 05:58:04 -0700, DB042188
wrote:

Never looked at vba/excel before, but was able to reduce run time on an app
by 75% via recorset streamlining.

Still not happy with run times, learned that manipulation of cells is
consuming most of the other 25%.

Learned that perhaps adding values in bulk to spreadsheet (rather than one
by one) would probably improve performance drastically.

So I'm wondering if the pretty substantial color, pattern and comment
manipulation we're also doing can somehow be applied in bulk, perhaps from an
array or similar workaround?

adding colors, patterns, comments to large spreadsheets in bul
 

I can give that a whirl but isnt the idea to source the color, pattern etc
decision from something not already in cells, which from my understanding is
the reason for poor performance in the first place?

What about comments, can I source them from an array similar to the way in
which I'll try to source values from an array?

"Gord Dibben" wrote:

Have a look at this sample code from Dave Peterson.

Sub ColorFormulas2()
'work on an entire range without For Each.........Next
Dim rng As Range

Set rng = Nothing
On Error Resume Next 'just in case there are no formulas
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If rng Is Nothing Then
'do nothing
Else
rng.Interior.Color = vbYellow
End If
End Sub


Gord Dibben MS Excel MVP

On Wed, 9 Apr 2008 05:58:04 -0700, DB042188
wrote:

Never looked at vba/excel before, but was able to reduce run time on an app
by 75% via recorset streamlining.

Still not happy with run times, learned that manipulation of cells is
consuming most of the other 25%.

Learned that perhaps adding values in bulk to spreadsheet (rather than one
by one) would probably improve performance drastically.

So I'm wondering if the pretty substantial color, pattern and comment
manipulation we're also doing can somehow be applied in bulk, perhaps from an
array or similar workaround?