CODEWITHSUNDEEP
vbaexcel
Eoin2211
Eoin2211

Reputation: 911

VBA Code Performance Issue

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

Upvotes: 1

Views: 98

Answers (2)

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 2

user7857211
user7857211

Reputation:

Try this:

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

And avoid Select statements, they are quite slow.

Upvotes: 0

Related Questions