Make Macro run faster

Question

I have this macro that I'm running in Excel, but it runs very slow , any sugestion to make it run faster?

It runs for almost 30 minutes, I know that my computer is not the newest, but its quite a bit of time.

Sub Macro1first_part()
    Sheets("IEX Adherence").Select    
    'Deletes all previous data
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    ActiveSheet.paste
    'Start of Macro sets the Search value of  "Signon" to be the number of times the macro loops
    Dim Search As String    
    'Insert Column
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    'Paste formula to look for Agnet Id        
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[1],'Agent DataBase'!C6,1,0),"" "")"
    Range("A1").Select
    Selection.Copy
    Columns("A:A").Select
    ActiveSheet.paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

   'Format Column to be able to work with Ids

    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$AB").AutoFilter Field:=1, Criteria1:="="
    Columns("A:A").Select
    Selection.ClearContents
     ActiveSheet.Range("$A:$AB").AutoFilter Field:=1
    ActiveWindow.SmallScroll Down:=-21
    ActiveSheet.Range("$A:$AB").AutoFilter Field:=2, Criteria1:="Signon"
    Columns("B:B").Select
    Selection.ClearContents
    ActiveSheet.Range("$A:$AB").AutoFilter Field:=2
    ActiveSheet.Range("$A:$AB").AutoFilter Field:=10, Criteria1:="Signon"
    Columns("J:J").Select
    Selection.ClearContents

    ActiveWindow.SmallScroll Down:=-15

    ActiveSheet.Range("$A:$AB").AutoFilter Field:=10

    Range("A1").Select
    'The actual search for the word "Signon"
     Dim wordCount As Long

    wordCount = Application.WorksheetFunction.CountIf(ActiveSheet.Cells, "Signon")

    For wordCount = 1 To 230
    Cells.Find(What:="Signon", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        ActiveCell.Offset(0, -3).Select
        Selection.Copy
        Selection.End(xlToLeft).Select
        Selection.End(xlToLeft).Select
        Selection.End(xlUp).Select  
        ActiveCell.Offset(0, 6).Select            
        ActiveSheet.paste
        ActiveCell.Offset(15, 1).Select                             Cells.FindNext(After:=ActiveCell).Activate
          Debug.Print wordCount
Next wordCount    
'macro finishes loop activitie
 Sheets("Agent DataBase").Select
 Range("A1").Select

MsgBox "Adherence Update Completed!"

End Sub

See this [post](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) about not using `Active` or `Select` statments. — Automate This, Mar 12 '14 at 19:36

score 1 · Answer 1 · answered Mar 12 '14 at 18:55

Get rid of all the .SELECTS for a start!

for example..

 Rows("1:1").Select
    Selection.AutoFilter

can be

Rows("1:1").AutoFilter

also turn off screen updating at the start of macro and back on at the end

Application.ScreenUpdating = False

Application.ScreenUpdating = True

This will be a good start!

Make Macro run faster

1 Answers1