VBA unique formula with variable-CodePudding

I'm having a really hard time setting the correct formula in VBA to output a list of unique values in a list.

    Dim lastRow As Long
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    Range("B1").Select
    ActiveCell.Formula2R1C1 = "=UNIQUE(RC[-1]:" & lastRow & ")"

Pretty simple what I'm trying to do. Just use the range from cell A1 to the last row in the that column and push it to the unique formula.

CodePudding user response：

A1 notation:

Range("B1").Formula2 = "=UNIQUE(A1:A" & lastRow & ")"

R1C1 notation:

Range("B1").Formula2R1C1 = "=UNIQUE(RC[-1]:R[" & lastRow & "]C[-1])"