Macro suddenly causes the screen to blink
 

On Thu, 25 Nov 2004 10:43:14 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Top

Application.ScreenUpdating = False

'your code that moves things

Application.ScreenUpdating = True

That made a huge difference -- thanks.

Also note that "selecting" things(which causes the flashing around)is usually
not necessary.

Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

is equal to.....

Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

and will not cause flashing because no ranges or sheets are actually selected.

I don't understand this syntax and cannot find it in the help. Is this
a command? a property? ???

What is the ":=" syntax? Is it documented anywhere?

Here is a snippet of code. It's a loop to reset the color and bold
attributes of a bunch of cells and to examine the values. Is there a
way to do this without selecting the cells?

Thanks



For iRow = iRowPC1 To iRowPC2 'Loop through all cells
Range(sColTlyLet & iRow).Select 'Position at next tally cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
End With
Range(sColSumLet & iRow).Select 'Position at next sum cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nSum(iRow) = .Value 'Save the value for comparison
End With
Range(sColPCDLet & iRow).Select 'Position at % next cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nPCD(iRow) = .Value 'Save the value for comparison
Next iRow


--
Email: Usenet-20031220 at spamex.com
(11/09/04)

why not try just
with range(yourrange).font
..ColorIndex = 0
..Bold = False
End With



--
Don Guillett
SalesAid Software

"Top Spin" wrote in message
...
On Thu, 25 Nov 2004 10:43:14 -0800, Gord Dibben <gorddibbATshawDOTca
wrote:

Top

Application.ScreenUpdating = False

'your code that moves things

Application.ScreenUpdating = True

That made a huge difference -- thanks.

Also note that "selecting" things(which causes the flashing around)is
usually
not necessary.

Range(Range("A1"), Range("A1").End(xlDown)).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
Selection.Paste

is equal to.....

Range(Range("A1"), Range("A1").End(xlDown)).Copy _
Destination:=Sheets("Sheet2").Range("A1")

and will not cause flashing because no ranges or sheets are actually
selected.

I don't understand this syntax and cannot find it in the help. Is this
a command? a property? ???

What is the ":=" syntax? Is it documented anywhere?

Here is a snippet of code. It's a loop to reset the color and bold
attributes of a bunch of cells and to examine the values. Is there a
way to do this without selecting the cells?

Thanks



For iRow = iRowPC1 To iRowPC2 'Loop through all cells
Range(sColTlyLet & iRow).Select 'Position at next tally cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
End With
Range(sColSumLet & iRow).Select 'Position at next sum cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nSum(iRow) = .Value 'Save the value for comparison
End With
Range(sColPCDLet & iRow).Select 'Position at % next cell
With ActiveCell
.Font.ColorIndex = iColorAuto 'Reset it to black
.Font.Bold = False '.& make it unbold
nPCD(iRow) = .Value 'Save the value for comparison
Next iRow


--
Email: Usenet-20031220 at spamex.com
(11/09/04)

On Sun, 28 Nov 2004 11:52:05 -0600, "Don Guillett"
wrote:

why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With

I'll try that. My code was obtained by modifying code from recording a
macro.

And "yourrange" can be any rectangular range, right?

That will work for resetting all of the cells to the same value, but I
also need to examine each cell value. Actually, store it in an array.
Is there a way to store an entire range (column) in consecutive
elements of an array with a single statement?

Thanks

--
Email: Usenet-20031220 at spamex.com
(11/09/04)

On Sun, 28 Nov 2004 11:52:05 -0600, "Don Guillett"
wrote:

why not try just
with range(yourrange).font
.ColorIndex = 0
.Bold = False
End With

Thanks to everyone for the help. The macro now works almost
instantaneously. Even if I leave screen updating on, it only takes a
fraction of the time it used to take. Sweet!

Is there a way to assign the values in a range of cells to an array in
a single statement? If that is possible, I could eliminate all loops.

Thanks

--
Email: Usenet-20031220 at spamex.com
(11/09/04)