Hi there,
I have a piece of code which is working beautifully but at 500000 lines it is occasionally causing out of Memory issues when I run some other macros after it because it stores the actual formula in the cell. I wish for the Macro to evaluate the formula in relevant to the row it is in but only paste the value as text in the cell instead of having 6 coloumns * 500000 lines of formula saved in cells:

Here is the code:
Sub ConcatFullImport()
Dim LastRow As Long
    Dim Ws As Worksheet
    Application.ScreenUpdating = 0

    Set Ws = Sheets("FullImport")
    If Ws.Range("G13").Value = "" Then Exit Sub
    Ws.Range("A13:F" & Rows.Count).ClearContents

    LastRow = Ws.Range("AS" & Ws.Rows.Count).End(xlUp).Row
    Ws.Range("A13:A" & LastRow).Formula = "=IF(ISNA(MID(INDEX(Data!$D$4:$D$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,4)),"""",MID(INDEX(Data!$D$4:$D$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,4))"
    Ws.Range("B13:B" & LastRow).Formula = "=IF(ISNA(MID(INDEX(Data!$B$4:$B$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,50)),"""",MID(INDEX(Data!$B$4:$B$20,MATCH(CONCATENATE(C13,D13,E13),Data!$C$4:$C$20,0)),1,50))"
    Ws.Range("C13:C" & LastRow).Formula = "=IF(K13="""","""",K13)"
    Ws.Range("D13:D" & LastRow).Formula = "=IF(L13="""","""",L13)"
    Ws.Range("E13:E" & LastRow).Formula = "=IF(CONCATENATE(C13,D13)=""UR"","""",IF(S13="""","""",S13))"
    Ws.Range("F13:F" & LastRow).Formula = "=CONCATENATE(H13,I13,J13)"
    
    Application.ScreenUpdating = True
    Ws.Calculate
End Sub
Is it possible to manipulate this VBA to paste only the values from Row 1 to 500000 down coloumns A to F?