• 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)

I

indi visual

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?
 
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
 
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.
 
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
 
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 />
 
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.
 
You must log in or register to reply here.
Back
Top