• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Faster Calculations (By Making VB Code Shorter)

indi visual

New Member
I have a lot of spaces in between my vb code.

For example:


My_Sub_Name()

...and then like 8 spaces here...

If Range("Z1") = 0 Then

Application.EnableEvents = False

Range("Z9").Select

Selection.ClearContents


...and then more spaces...


End Sub


Will it make my code run any smoother if I condense the extra empty spaces between the lines of code?
 

TessaES

New Member
I don't think so.

But it will probably get faster if you don't select cells to perform an action. For example, the last two lines of your code sample could be written as:


Range("Z9").ClearContents
 

xld

Member
Absolutely not!


You could turn off screenupdating and set calculation to manual at the start and reset at the end as well as not selecting.
 

Hui

Excel Ninja
Staff member
Indi

There is a few good articles on the web about techniques for optimising VBA in Excel

have a read of some in particular http://www.cpearson.com/excel/optimize.htm
 

indi visual

New Member
I have an interesting question that'll probably warrant a good laugh (at me likely) : )


How do I make this code shorter?

<br />
Private Sub Worksheet_Calculate()<br />
Application.ScreenUpdating = False<br />
If Range("Z39") = "1" Then<br />
Application.EnableEvents = True<br />
Range("Z39").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-38]C, ""1"")"<br />
Range("Z39").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "inactive"<br />
Range("Z9").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIFS(R[1]C[-19]:R[991]C[-19],TODAY(),R[1]C[-5]:R[991]C[-5],"""")+COUNTIFS(R[1]C[-19]:R[991]C[-19],""<""&TODAY(),R[1]C[-5]:R[991]C[-5], """")"<br />
Application.Wait Now + TimeSerial(0, 0, 6)<br />
Range("Z9").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "26"<br />
Application.EnableEvents = True<br />
End If<br />
Application.EnableEvents = False</p>
<p>Range("Z38").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-37]C, ""0"")"</p>
<p>Range("Z39").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-38]C, ""1"")"</p>
<p>Range("Z40").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-39]C, ""2"")"</p>
<p>Range("Z41").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-40]C, ""3"")"</p>
<p>Range("Z42").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-41]C, ""4"")"</p>
<p>Range("Z43").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-42]C, ""5"")"</p>
<p>Range("Z44").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-43]C, ""6"")"</p>
<p>Range("Z45").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-44]C, ""7"")"</p>
<p>Range("Z46").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-45]C, ""8"")"</p>
<p>Range("Z47").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-46]C, ""9"")"</p>
<p>Range("Z48").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-47]C, ""10"")"</p>
<p>Range("Z49").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-48]C, ""11"")"</p>
<p>Range("Z50").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-49]C, ""12"")"</p>
<p>Range("Z51").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-50]C, ""13"")"</p>
<p>Range("Z52").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-51]C, ""14"")"</p>
<p>Range("Z53").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-52]C, ""15"")"</p>
<p>Range("Z54").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-53]C, ""16"")"</p>
<p>Range("Z55").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-54]C, ""17"")"</p>
<p>Range("Z56").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-55]C, ""18"")"</p>
<p>Range("Z57").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-56]C, ""19"")"</p>
<p>Range("Z58").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-57]C, ""20"")"</p>
<p>Range("Z59").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-58]C, ""21"")"</p>
<p>Range("Z60").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-59]C, ""22"")"</p>
<p>Range("Z61").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-60]C, ""23"")"</p>
<p>Range("Z62").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-61]C, ""24"")"</p>
<p>Range("Z63").Select<br />
Selection.ClearContents<br />
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-62]C, ""25"")"</p>
<p>Application.ScreenUpdating = True</p>
<p>End Sub<br />
 

indi visual

New Member
Ha ha ha lol


I figured it out.

[pre]</p>
<p> Application.Goto Reference:="R65C26:R90C26"<br />
Selection.Copy<br />
Application.Goto Reference:="R38C26:R63C26"<br />
ActiveSheet.Paste</p>
<p>[/pre]


I pasted the same thing beneath, and I use this code to copy and paste it.


I'm sure I get away with the most messy crazy code sometimes, but as long as it works I'm all for it.
 
Top