VBA unique formula with variable

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
    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.

>Solution :

A1 notation:

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

R1C1 notation:

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

Leave a Reply