Solved VBA to Insert LastRow into a Formula to count Unique Values

Microsoft Excel 2010 - complete product...
June 25, 2018 at 04:18:39
Specs: Windows 7, 2,4 GHz / 4 GB
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?


See More: VBA to Insert LastRow into a Formula to count Unique Values

Report •

#1
June 25, 2018 at 10:33:37
✔ Best Answer
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


Report •

#2
June 25, 2018 at 22:34:15
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!


Report •
Related Solutions


Ask Question