Excel freezes during VBA formula copy down (200,000 rows +)

Question

I am fairly new to VBA so have one basic code which I am using for quite a lot of my templates and they work perfectly fine but as this one has more than 200,000 rows and the macro freezes or takes 20 min to complete. do you know any ideas or anything how I could make it quicker and not freeze at all please?

macro is here (simply removing trailing space from one column, then dragging all the formulas in first row down to the last row of particular column D)

is there anything I can add to my VBA code to speed it up or will this always be a problem due to large amount of data in the rows? many thanks for your help

Sub Fill_formulas_Click() 

Dim LR As Long

Columns("E:E").Select

Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False 

LR = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row

Range("A2").AutoFill Destination:=Range("A2:A" & LR)
Range("B2").AutoFill Destination:=Range("B2:B" & LR)
Range("C2").AutoFill Destination:=Range("C2:C" & LR)
Range("N2").AutoFill Destination:=Range("N2:N" & LR)
Range("O2").AutoFill Destination:=Range("O2:O" & LR)
Range("P2").AutoFill Destination:=Range("P2:P" & LR)
Range("Q2").AutoFill Destination:=Range("Q2:Q" & LR)
Range("R2").AutoFill Destination:=Range("R2:R" & LR)
Range("S2").AutoFill Destination:=Range("S2:S" & LR)
Range("T2").AutoFill Destination:=Range("T2:T" & LR)
Range("U2").AutoFill Destination:=Range("U2:U" & LR)

End Sub

Answer 1

One thing to try is to turn off screenupdating and calculation before you start:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


*your_routines*

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

If you might have Event Code active in your worksheet, you should also turn that off:

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

*your_code*

With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

Answer 2

In addition to what Ron Rosenfeld provided, simplify the code to the below to reduce the amount of actions taken (screen updating code goes before the Dim LR as Long statement):

Sub Fill_formulas_Click()

    Dim LR As Long
    LR = ActiveSheet.Range("D" & Rows.Count).End(xlUp).Row

    Range("E1:E" & LR).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Range("A2:C2").AutoFill Destination:=Range("A2:C" & LR)
    Range("N2:P2").AutoFill Destination:=Range("N2:U" & LR)

End Sub