I am using VBA to insert a formula which will count the unique values in a previously selected column. The formula is inserted in a cell of my choosing via InputBox. It's all working great if I select only the used range of a column then run the code. But if I select the whole column then the formula will return a #N/A error. So I thought of adding LastRow to the formula and limit its reach for the used range only which is normally about 5000 rows, but dynamic of course.

I'm posting the relevant part of the code below:

Set Rng = Selection LastRow = .Cells(Rows.Count, Rng.Columns.Column).End(xlUp).Row cell.Cells(1, 1).FormulaLocal = _ "=SUM(IF(FREQUENCY(MATCH(" & Rng.Address(0, 0) & ";" & Rng.Address(0, 0) & _ ";0);MATCH(" & Rng.Address(0, 0) & ";" & Rng.Address(0, 0) & ";0))>0;1))"How could I insert LastRow into the formula so it actually works?

Have you tried something like this? Set the "Rng" after you've determined it's size. myCol = Selection.Column lastCell = Cells(Rows.Count, myCol).End(xlUp).Row Set Rng = Range(Cells(1, myCol), Cells(lastCell, myCol))

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

What I tried was to mess with the formula instead of thinking to set the Rng right. I still have a lot to learn. Thanks for the speedy solution, it works a treat!

Ask Your Question

Weekly Poll