VBA Tips, Tricks and useful Code Snippets

[JumboCactuar]

If you have any to add to this, appreciate it if you would post any you use regularly. I'm fairly new to vba and stuff like this helps with learning. Sorry if something like this has been posted before but couldn't see any:

1. Copy + Paste Values
Instead of this:

Range("A1:A10").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues

Do this:

Range("D1:D10").Value2 = Range("A1:A10").Value2

---

2. 1 Liner IF Statement
If you are only checking for 1 condition you can simply do

If Range("A1") = "Red" Then Range("B1") = 1

---

3. Reference VBA Sheet Names

Instead of:

Sheets("Sheet1").Range("D1:D10").Value2 = Sheets("Sheet1").Range("A1:A10").Value2

use:

MainSheet.[D1:D10].Value2 = MainSheet.[A1:A10].Value2

This way if you change the sheet tab names, all your macros will continue to work.

---

4. Refresh all Pivot Tables in Workbook

Sub RefreshPivots()
Dim WS As Worksheet
Dim PT As PivotTable
    
    For Each WS In Worksheets
            For Each PT In WS.PivotTables
                PT.RefreshTable
            Next PT
    Next WS

End Sub

---

5. Find/Replace values in a Range

Sub NoBlanks()
Range("A1:D50").Select
    For Each r In Selection
        If r.Text = "" Then
            r.Value = 0
        End If
    Next r
End Sub

 

[My Aswer Is This]

Number 1 Could be done like this:

Range("A1:A10").Copy
Range("D1").PasteSpecial Paste:=xlPasteValues

 

[My Aswer Is This]

Sheets("Grape").Range("A1").Copy Sheets("Apple").Range("G1")

 

[My Aswer Is This]

Sheets("Grape").[A1].Resize(, 5).Copy Sheets("Apple").[D3]

 

[shg]

MainSheet.[D1:D10].Value2 = MainSheet.[A1:A10].Value2

The brackets cause the argument to be evaluated by Excel, not VBA. It uses an unnecessary, out-of-process function call and is slow.

 

[JumboCactuar]

MainSheet.[D1:D10].Value2 = MainSheet.[A1:A10].Value2

The brackets cause the argument to be evaluated by Excel, not VBA. It uses an unnecessary, out-of-process function call and is slow.

Thanks for the heads up.

So this is better?

Mainsheet.Range("D1:D10").Value2

 

[shg]

Depends, I reckon. For code that is running interactively with a user who will never notice milliseconds versus microseconds, it doesn't matter. For code operating in a busy loop, it could get ugly.

 

[My Aswer Is This]

I do always wonder what "Slow" means.
Some people think if a script takes. 2.7 seconds to run down 100K rows of data that is slow.
Others think .247 seconds is "Slow"