Speeding up a selection change

[bademployee]

Hi all,

Is is possible to speed up the following?:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("c6") = "" Then Range("c6") = "Blend Name"
    If Range("c42") = "" Then Range("c42") = "Blend Name"
    If Range("c78") = "" Then Range("c78") = "Blend Name"
    If Range("c114") = "" Then Range("c114") = "Blend Name"
    If Range("c150") = "" Then Range("c150") = "Blend Name"
    If Range("c186") = "" Then Range("c186") = "Blend Name"
    If Range("c222") = "" Then Range("c222") = "Blend Name"
    If Range("c258") = "" Then Range("c258") = "Blend Name"
    If Range("c294") = "" Then Range("c294") = "Blend Name"
    If Range("c330") = "" Then Range("c330") = "Blend Name"
    If Range("h22") = "" Then Range("h22") = "kg/Ha"
    If Range("n22") = "" Then Range("n22") = "ml/Ha"
    If Range("h58") = "" Then Range("h58") = "kg/Ha"
    If Range("n58") = "" Then Range("n58") = "ml/Ha"
    If Range("h94") = "" Then Range("h94") = "kg/Ha"
    If Range("n94") = "" Then Range("n94") = "ml/Ha"
    If Range("h130") = "" Then Range("h130") = "kg/Ha"
    If Range("n130") = "" Then Range("n130") = "ml/Ha"
    If Range("h166") = "" Then Range("h166") = "kg/Ha"
    If Range("n166") = "" Then Range("n166") = "ml/Ha"
    If Range("h202") = "" Then Range("h202") = "kg/Ha"
    If Range("n202") = "" Then Range("n202") = "ml/Ha"
    If Range("h238") = "" Then Range("h238") = "kg/Ha"
    If Range("n238") = "" Then Range("n238") = "ml/Ha"
    If Range("h274") = "" Then Range("h274") = "kg/Ha"
    If Range("n274") = "" Then Range("n274") = "ml/Ha"
    If Range("h310") = "" Then Range("h310") = "kg/Ha"
    If Range("n310") = "" Then Range("n310") = "ml/Ha"
    If Range("h346") = "" Then Range("h346") = "kg/Ha"
    If Range("n346") = "" Then Range("n346") = "ml/Ha"
End Sub

Thanks

Mark

 

[Fluff]

That shouldn't take very long, do you have any other event code on that sheet?
Also, do you have a lot of formulae on the sheet?

 

[offthelip]

One of the main actions that slows down Vba is the time taken to access the worksheet. this is often the bottleneck.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action. So you can halve the time taken by loading all the data which you are testing in one access to the worksheet, by loading the data into a variant array.
You could do get rid of the other access as well where you are writng to the worksheet, but this depends on whether there are formual in the area as well.


'load all the data into a variant araray

Inarr=range(cells(1,1),cells(346,14))


if(inarr(6,3)) = "" Then Range("c6") = "Blend Name"
if(inarr(42,3))= "" Then Range("c42") = "Blend Name"
if(inarr(78,3))= "" Then Range("c78") = "Blend Name"
if(inarr(114,3))= "" Then Range("c114") = "Blend Name"
if(inarr(150,3))= "" Then Range("c150") = "Blend Name"
if(inarr(186,3))= "" Then Range("c186") = "Blend Name"
if(inarr(222,3))= "" Then Range("c222") = "Blend Name"
if(inarr(258,3))= "" Then Range("c258") = "Blend Name"
if(inarr(294,3))= "" Then Range("c294") = "Blend Name"
if(inarr(330,3))= "" Then Range("c330") = "Blend Name"
if(inarr(22,8))= "" Then Range("h22") = "kg/Ha"
if(inarr(22,14))= "" Then Range("n22") = "ml/Ha"
if(inarr(58,8))= "" Then Range("h58") = "kg/Ha"
if(inarr(58,14))= "" Then Range("n58") = "ml/Ha"
if(inarr(94,8))= "" Then Range("h94") = "kg/Ha"
if(inarr(94,14))= "" Then Range("n94") = "ml/Ha"
if(inarr(130,8))= "" Then Range("h130") = "kg/Ha"
if(inarr(130,14))= "" Then Range("n130") = "ml/Ha"
if(inarr(166,8))= "" Then Range("h166") = "kg/Ha"
if(inarr(166,14))= "" Then Range("n166") = "ml/Ha"
if(inarr(202,8))= "" Then Range("h202") = "kg/Ha"
if(inarr(202,14))= "" Then Range("n202") = "ml/Ha"
if(inarr(238,8))= "" Then Range("h238") = "kg/Ha"
if(inarr(238,14))= "" Then Range("n238") = "ml/Ha"
if(inarr(274,8))= "" Then Range("h274") = "kg/Ha"
if(inarr(274,14))= "" Then Range("n274") = "ml/Ha"
if(inarr(310,8))= "" Then Range("h310") = "kg/Ha"
if(inarr(310,14))= "" Then Range("n310") = "ml/Ha"
if(inarr(346,8))= "" Then Range("h346") = "kg/Ha"

 

[Rick Rothstein]

Why do you have that code in a SelectionChange event procedure given that none of the code is dependent on the actual cell selected? You do realize that putting the code in a SelectionChange event procedure means it will be executed every time you select a different cell or range of cells even if you don't do anything with those cells.

As for speeding the code up, IF the cells you are testing for being equal to "" are truly blank (that is, there are NO formulas in any of them), then you could replace all of the If..Then code lines you posted with these three code lines which should execute more quickly (although I agree with Fluff that your code should not be all that slow to begin with)...

[table="width: 500"]
[tr]
	[td]Intersect(Range("6:6,42:42,78:78,114:114,150:150,186:186,222:222,258:258,292:294,330:330"), [C:C]) = "Blend Name"
Intersect(Range("22:22,58:58,94:94,130:130,166:166,202:202,238:238,274:274,310:310,346:346"), [H:H]) = "kg/Ha"
Intersect(Range("22:22,58:58,94:94,130:130,166:166,202:202,238:238,274:274,310:310,346:346"), [N:N]) ="ml/Ha"[/td]
[/tr]
[/table]

 

[bademployee]

Thanks everyone for your replies.

You're right, the event is pretty quick anyhow. I was going through the workbook speeding up various pieces and was curious to if there know what could be done as, there are many more similar pieces of code.

Due to lack of VBA knowledge I possibly structured formulas inefficiently, but needed the event triggered on a selection change because a few IF formulas exist based on the cells mentioned.

I implemented your suggestion offthelip & will use the variant array for other similar situations.

Many thanks