Using CountIf with variable range

Question

The following line of code works perfectly in my Excel VBA program:

UniqueDistilleries = wsBuyGuide.Evaluate("=SUM(IFERROR(1/COUNTIF(A2:A227,A2:A227),0))")

This line returns the number of unique distilleries in column A (cell A1 contains a title so I start the range at A2). However, as I add records, the last row will not always be A227. I'm trying to substitute a variable for 227 but can't seem to get it to work. The variable s denotes the last line in the spreadsheet. It's dimensioned as an integer. I'm using the variable in several other formulas (but not a CountIf) immediately preceding the above line of code so I know the variable is working correctly but I can't seem to get it to work here.

I'd appreciate any help you might give me to figure this out. Thanks.

Answer 1

Show your code or try this:

Dim e As Integer
e = 227
UniqueDistilleries = wsBuyGuide.Evaluate("=SUM(IFERROR(1/COUNTIF(A2:A" & e & ",A2:A" & e & "),0))")