VBA Code Performance Issue

Question

I am using a VBA macro to insert a column whereby it searches for a text character in column A. My code runs correctly. However, the file appears like it is going to crash. I will be building on the macro and want it to run smoothly. Is there a way to optimise my code

Code:

Sheets("Annual Rec").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.NumberFormat = "General"
Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=ISTEXT(RC[-1])"
Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
   :=False, Transpose:=False
Application.CutCopyMode = False

Try to [Avoid Select/Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — Rdster, Apr 21 '17 at 10:38
You can start by removing your 3 x `Select` and 4 x `Selection` — Shai Rado, Apr 21 '17 at 10:38
And change your `Copy`/`Paste` to `Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value = Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row).Value` — Jordan, Apr 21 '17 at 10:39

score 2 · Accepted Answer · answered Apr 21 '17 at 10:46

2

The code below does the same as yours, just without the unnecessary Select and Selection.

Option Explicit

Sub CopyColPasteVal()

Application.ScreenUpdating = False

With Sheets("Annual Rec")
    .Columns("B:B").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    .Columns("B:B").NumberFormat = "General"
    With .Range("B2:B" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=ISTEXT(RC[-1])"
        .Copy
        .PasteSpecial xlPasteValues
    End With
    Application.CutCopyMode = False
End With

Application.ScreenUpdating = True

End Sub

answered Apr 21 '17 at 10:46

Shai Rado

33,032
6
29
51

Now add in something to say that `.Copy` `.PasteSpecial xlPasteValues` is equivalent to `.Value = .Value` ;) – YowE3K Apr 21 '17 at 10:47
@YowE3K going to take a nap, I'll let you upgrade my answer if you feel like it ;) – Shai Rado Apr 21 '17 at 10:48
I'm going to sleep for the night, no short nap breaks for me! :) – YowE3K Apr 21 '17 at 10:49

score 0 · Answer 2 · answered Apr 21 '17 at 10:39

0

Try this:

Application.ScreenUpdating = False
'your code
Application.ScreenUpdating = True

And avoid Select statements, they are quite slow.

answered Apr 21 '17 at 10:39

VBA Code Performance Issue

2 Answers2