Another way that I often find useful, particularly if LR needs to be incorporated in several places in the formula, is as follows.

Rather than breaking the formula up into bits and manually sticking LR in each relevant place is to write the formula as you want it but with a placeholder for LR then replace the placeholder(s) in bulk.

To me it has the added advantage of making the formula more readable.

Something like this

Code:

`Const f As String = "=COUNTIF(RC[2]:R#C[2],RC[2])" With .Range("A2:A" & LR) .Formula = Replace(f, "#", LR, 1, -1, 1) .Value = .Value End With`

Also, if we know we are staring in row 2 (which appears to be the case form your loop) and counting in column C then my Const statement would become ..

Code:

`Const f As String = "=COUNTIF(C2:C$#,C2)"`

.. which is much easier to read as it looks pretty much like a standard worksheet formula.