Mrrrr's Forum
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
Lista Forumurilor Pe Tematici
Mrrrr's Forum | Lista de useri | Inregistrare | Login


Nu sunteti logat.
Nou pe simpatie:
25 ani
cauta Barbat
25 - 35 ani
Mrrrr's Forum / Tutoriale si Ghiduri Utile // Tutorials and useful guides / [EXCEL] Count the number of unique values in a selected column [VBA] Moderat de TonyTzu  
Mesaj Pagini: 1
Membru activ

Inregistrat: acum 1 an
Postari: 331
The code below will count unique values in a selected (dynamic) column. You can select the whole column, not just the used range.

Tthanks to DerbyDad03 for helping me set lastCell and Rng properly: ... 21324.html

Sub CountUniqueFormula()
Application.ScreenUpdating = False

Dim Rng As Range
Dim cell As Range
Dim lastCell, firstCell As Long
Dim myCol As Integer

With ActiveSheet

Set cell = Application.InputBox(prompt:="Select CELL FOR FORMULA:", Type:=8)
On Error Resume Next

If cell Is Nothing Then
MsgBox "cancel pressed"

myCol = Selection.Column 'thanks DerbyDad03 @
lastCell = .Cells(Rows.Count, myCol).End(xlUp).Row 'thanks DerbyDad03 @
firstCell = .Cells(lastCell, myCol).End(xlUp).Row
Set Rng = Range(Cells(firstCell, myCol), Cells(lastCell, myCol)) 'thanks DerbyDad03 @

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))"

End If
End With

Application.ScreenUpdating = True
End Sub

FIXED: this doesn't work if first cell in the range is empty.

Modificat de TRaP (acum 1 an)

pus acum 1 an

Inregistrat: acum 12 ani
Postari: 1567
Function to count unique values fast:

The following example shows a VBA user-defined function that uses the fact that the index to a collection must be unique. For an explanation of some of the techniques used, see the section about user-defined functions in the Using Functions Efficiently section of this article.

Public Function COUNTU(theRange As Range) As Variant
    Dim colUniques As New Collection
    Dim vArr As Variant
    Dim vCell As Variant
    Dim vLcell As Variant
    Dim oRng As Range

    Set oRng = Intersect(theRange, theRange.Parent.UsedRange)
    vArr = oRng
    On Error Resume Next
    For Each vCell In vArr
    If vCell <> vLcell Then
        If Len(CStr(vCell)) > 0 Then
             colUniques.Add vCell, CStr(vCell)
        End If
    End If
    vLcell = vCell
    Next vCell
    COUNTU = colUniques.Count
End Function

This formula, =COUNTU(A2:A11000), takes only 0.061 seconds, giving an improvement factor of 13.8/0.061=226.

Source: ... ottlenecks


pus acum 10 zile
Pagini: 1    
Mergi la