Recorded Macro Code tweak

[zaska]

Good Evening to everyone..

I have recorded a macro code...but it is taking lot of time for getting the output..Can anyone help me to tweak my code..by cleaning the unnecessary lines to execute the code more quickly

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/04/2011 by ABS
'
' Keyboard Shortcut: Ctrl+a
'
    ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\c.csv"
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="=ET", Operator:=xlOr, _
        Criteria2:="=BT"
    Range("B:B,G:G,H:H,J:J").Select
    Range("J1").Activate
    Selection.AutoFilter Field:=2
    Selection.Delete Shift:=xlToLeft
    Columns("G:G").Select
    Selection.Cut
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
    Selection.NumberFormat = "yyyymmdd"
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;E:\Macros\Input\m.DAT", Destination:=Range("A1"))
        .Name = "MTO_04012010"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("A:A,B:B,E:E,G:G").Select
    Range("G1").Activate
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="=ET", Operator:=xlOr, _
        Criteria2:="=BE"
    Sheets("c").Select
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Sheet1!C[-7]:C[-5],3,0)"
    Range("H1").Select
    Selection.AutoFill Destination:=Range("H1:H1333")
    Range("H1:H1333").Select
    Range("H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ChDir "E:\Macros\Output"
    ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\c.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = False
    ActiveWindow.Close
    ActiveWindow.Close
End Sub

Thank u

 

[CWatts]

I dont have a lot of time right now, but the first thing I'll reccommend is to always add these commands to any macro. These generally make a huge difference in execution speed.

Add this to the start of your code:

Application.ScreenUpdating = False
Application.Calculation = xlManual

Add this to the very end of your code:

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

 

[Joe4]

In addition to the good tips from the previous post, most sections of code where one line ends in "Select" and the next one start "Selection" can be combined into one (most of the time, it is not necessary to select the cells, and doing so actually will slow the code down).

For example, this:

    Columns("G:G").Select
    Selection.Cut

could be replaced by:

    Columns("G:G").Cut

The same can be said for ActiveCell.
So this:

    Range("H1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],Sheet1!C[-7]:C[-5],3,0)"

could be replaced by this:

    Range("H1").FormulaR1C1 = "=VLOOKUP(RC[-7],Sheet1!C[-7]:C[-5],3,0)"

So there are a bunch of parts of your code that can be cleaned up using this knowledge.

 

[zaska]

Thank u verymuch...I was not aware of it..and it did really make a difference after implementing your suggestion.


Looking for someother inputs from members..to tweak the code and increase it's execution speed

 

[Joe4]

Looking for someother inputs from members..to tweak the code and increase it's execution speed

Take a look at the post I made while you were replying to the previous one.

 

[JackDanIce]

You may want to update your code with the comments suggested so far and then repost; I had a look at it earlier and like CWatts, didn't have time to work through each line. Looks like you've been given some helpful suggestions to get you started and be a good way for you to learn too

 

[zaska]

Thank you everyone..

I learned valuable suggestions ...I will implement the same and repost the code for further tweaking it...so that i could learn more from it..