[RESOLVED] recorded macro - execution time very long

[shailee]

Hello i am an amateur in macros, have recorded one macro made few changes but when run it is very time consuming it take 10+ mins to execute. can someone help me out regarding wat changes should i make to make it more fast and would be more of a professional code sortaf.

Below is my code

Sub below100()
    Dim X As Long
    NumRows = Range("A20", Range("A20").End(xlDown)).Rows.Count

'1. below100 Macro
 
 Application.ScreenUpdating = False
 ActiveWorkbook.Worksheets("EVENING RAW").Sort.SortFields.Clear
     ActiveWorkbook.Worksheets("EVENING RAW").Sort.SortFields.Add Key:=Range( _
        "T20:T5900"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("EVENING RAW").Sort
        .SetRange Range("A19:BK5900")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    Rows("19:19").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$19:$Bz$65536").AutoFilter Field:=20, Criteria1:="<100" _
        , Operator:=xlAnd
    Range("T20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
 ActiveSheet.ShowAllData
    

'2 phase Delete Macro
   Range("BL20").Select
    
    For X = 1 To NumRows
         ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-62],PHASE!R12C2:R[64980]C[-62],1,0)"
         ActiveCell.Offset(1, 0).Select
      Next
       
    Columns("BL:BL").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    ActiveSheet.Range("$A$19:$BZ$65536").AutoFilter Field:=64, Criteria1:= _
        "<>#N/A", Operator:=xlAnd
        
        Range("BL20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData
 

    
'3 nottocall Macro
    Range("BL20").Activate
    Range("B20:B65536").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.TextToColumns Destination:=Range("B20"), DataType:=xlDelimited, _
        TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=True, Semicolon _
        :=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, _
        1), TrailingMinusNumbers:=True
    Selection.NumberFormat = "0.00000000"
    
    Workbooks.Open Filename:= _
        "\\inahdnas\FUNCTIONAL_DIR\BNC\CREDIT MONITORING\Dev\Not_To_ Call_ List.xls"
    ThisWorkbook.Activate
                  
    Range("BL20").Select
    
    For X = 1 To NumRows
         ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-62],'[Not_To_ Call_ List.xls]Sheet1'!R2C2:R65536C2,1,0)"

         ActiveCell.Offset(1, 0).Select
      Next
    
   Windows("Not_To_ Call_ List.xls").Close
   ThisWorkbook.Activate
 
    Columns("BL:BL").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

   ActiveSheet.Range("$A$19:$BL$12739").AutoFilter Field:=64, Criteria1:= _
        "<>#N/A", Operator:=xlAnd
        
        Range("BL20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
   ActiveSheet.ShowAllData
 
'4. Morning thresh
    
    Range("BL20").Select
    For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
             "=VLOOKUP(RC[-60],'MORNING RAW'!R20C4:R[13047]C[-44],17,0)"
         ActiveCell.Offset(1, 0).Select
      Next
             
    Columns("BL:BL").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
' diff
     
     
        Range("BM20").Select
    For X = 1 To NumRows
   
   ActiveCell.FormulaR1C1 = "=RC[-45]-RC[-1]"
       
         ActiveCell.Offset(1, 0).Select
      Next
      
      Columns("BM:BM").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Columns("BL:BL").Select
    
    Selection.Delete Shift:=xlToLeft
    
    Range(" BL19").Value = " &#37; HIKE"

'5. Vlookup exp from morning

        Range("BM20").Select
            
        For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-61],'MORNING RAW'!R20C4:R[65536]C[-49],13,0)"
  
         ActiveCell.Offset(1, 0).Select
      Next
          
    Columns("BM:BM").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
   ' Exp. hike
   
     Range("BN20").Select
        For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
        "=RC[-1]-RC[-49]"
  
         ActiveCell.Offset(1, 0).Select
      Next
               
          
    Columns("BN:BN").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
         Columns("BM:BM").Select
    
    Selection.Delete Shift:=xlToLeft
        
     Range(" BM19").Value = " EXPOSURE HIKE"
        
        
'6. Delete <30% hike & <500 Rs exp hike excluding new cases

    ActiveSheet.Range("$A$19:$BM$2948").AutoFilter Field:=64, Criteria1:="<=29" _
        , Operator:=xlAnd
    Range("BM19").Select
    ActiveSheet.Range("$A$19:$BM$2948").AutoFilter Field:=65, Criteria1:= _
        "<=499", Operator:=xlAnd
    Range("BM20").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData


'7. CITYTOZONE Mapping
   Range(" BN19").Value = " ZONE "
    
    Workbooks.Open Filename:= _
        "\\inahdnas\FUNCTIONAL_DIR\BNC\CREDIT MONITORING\Dev\Cities to Zones1.xls"
    ThisWorkbook.Activate
    Range("BN20").Select
     
      For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-60],'[Cities to Zones1.xls]ar_ct'!R2C1:R65536C3,3,0)"
         ActiveCell.Offset(1, 0).Select
      Next
         
    Windows("Cities to Zones1.xls").Close
    ThisWorkbook.Activate
    
    
    Columns("BN:BN").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
  
'9. WATCHLIST Macro
    Range(" BO19").Value = " WATCHLIST"
    Workbooks.Open Filename:= _
        "\\inahdnas\FUNCTIONAL_DIR\BNC\CREDIT MONITORING\Dev\Watchlist.xls"
    ThisWorkbook.Activate
  
    Range("Bo20").Select
        For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-65],[Watchlist.xls]Sheet1!R2C3:R65536C6,4,0)"
  
         ActiveCell.Offset(1, 0).Select
      Next
                
           Windows("Watchlist.xls").Close
   ThisWorkbook.Activate
 
    Columns("BO:BO").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
'10. ECSSI
    Range(" BP19").Value = " ECS-SI"
    Workbooks.Open Filename:= _
        "\\inahdnas\FUNCTIONAL_DIR\BNC\CREDIT MONITORING\Dev\ECS-SI.xls"
    ThisWorkbook.Activate
    
    Range("BP20").Select
   For X = 1 To NumRows
        ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-66],'[ECS-SI.xls]Sheet1'!R2C2:R65536C3,2,0)"
  
         ActiveCell.Offset(1, 0).Select
      Next
        
        Windows("ECS-SI.xls").Close
    ThisWorkbook.Activate
          
    Columns("BP:BP").Select
        Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
          Application.ScreenUpdating = True
End Sub

i'm sorry this is very long but please if someone could help me with this.also is any declaration required in this??
Thanks in advance