Unique formula to return data to last row

[windb]

Please can someone tell me how I can amend this formula so that it returns all numbers in column F up to the last row, i.e. so that I can use it repeatedly on workbooks which have different rows of data.

    Sheets("Lists").Select
    Range("A2").Select
    ActiveCell.Formula2R1C1 = _
        "=SORT(UNIQUE(FILTER('Report'!RC[5]:R[468]C[5]))"
    Range("A3").Select
End Sub

Note - the last row has previously been declared as a variable within the existing macro I've built, as follows:

Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row

I've tried tagging "LR" onto the formula with no success.

Note- the "LR" variable is based on the no. of rows in a different sheet called "Report".

I am looking for the results to return to the "Lists" tab.

Many thanks

 

[Fluff]

In future when posting code, please use code code tags, rather than making everything bold How to Post Your VBA Code

How about

    Sheets("Lists").Select
    Range("A2").Formula2R1C1 = _
        "=SORT(UNIQUE(FILTER('Report'!RC[5]:R" & LR & "C[5]))"
    Range("A3").Select

 

[windb]

In future when posting code, please use code code tags, rather than making everything bold How to Post Your VBA Code

How about

    Sheets("Lists").Select
    Range("A2").Formula2R1C1 = _
        "=SORT(UNIQUE(FILTER('Report'!RC[5]:R" & LR & "C[5]))"
    Range("A3").Select

Many thanks Fluff. That works. I've removed the Filter part of the equation as it's not needed, and in any case, the formula doesn't work with it. Useful to know about code tags too. Great stuff.

 

[Fluff]

You're welcome & thanks for the feedback.