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

You can start by removing your 3 x Select and 4 x Selection — Shai Rado
– Shai Rado, Commented Apr 21, 2017 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
– Jordan, Commented Apr 21, 2017 at 10:39

Shai Rado · Accepted Answer · 2017-04-21 10:46:03Z

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, 2017 at 10:46

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

YowE3K Over a year ago

Now add in something to say that .Copy .PasteSpecial xlPasteValues is equivalent to .Value = .Value ;)

Shai Rado Over a year ago

@YowE3K going to take a nap, I'll let you upgrade my answer if you feel like it ;)

YowE3K Over a year ago

I'm going to sleep for the night, no short nap breaks for me! :)

user7857211 · Accepted Answer · 2017-04-21 10:39:49Z

0

Try this:

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

And avoid Select statements, they are quite slow.

answered Apr 21, 2017 at 10:39

user7857211

Collectives™ on Stack Overflow

VBA Code Performance Issue

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related