Vba worksheet formula from last row

[Trebor8484]

Hi all, I have a worksheet with thousands of rows of data. The code below works and recalculates the formula each time I add more data to the sheet and then converts the formula to values.

Does anyone have a solution where I can apply the formula but only from the last row in column A to avoid having to apply the formula to the whole column each time, due to the amount of rows Excel grinds to a halt now.

dstsht.Range("AZ2:AZ" & dstsht.Cells(Rows.Count, "A").End(xlUp).Row).Value = "" dstsht.Range("AZ2:AZ" & dstsht.Cells(Rows.Count, "A").End(xlUp).Row).Formula = "=IFERROR(VLOOKUP(D2,'YTD Report'!F:S,10,FALSE),IFERROR(VLOOKUP(D2,'New Report'!B:X,23,FALSE),IFERROR(VLOOKUP(MID(D2,2,2),'Calculation Sheet'!A:B,2,FALSE),""UNKNOWN"")))"

 

[pbornemeier]

This will add a calculation to the cell in column AZ that is in the same row as the last populated cell in column A.

    With dstsht.Range("AZ" & dstsht.Cells(Rows.Count, "A").End(xlUp).Row)
        .NumberFormat = "General"
        .Formula = "=IFERROR(VLOOKUP(D2,'YTD Report'!F:S,10,FALSE),IFERROR(VLOOKUP(D2,'New Report'!B:X,23,FALSE),IFERROR(VLOOKUP(MID(D2,2,2),'Calculation Sheet'!A:B,2,FALSE),""UNKNOWN"")))"
        Application.Calculate
        .Value = .Value
    End With