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

POZE MRRRR'S FORUM

Nu sunteti logat.
Nou pe simpatie:
IoanaAndreea
Femeie
25 ani
Constanta
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  
Autor
Mesaj Pagini: 1
TRaP
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:
https://www.computing.net/answers/offic ... 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"
Else

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

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
   
Mrrrr
AdMiN

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:
https://docs.microsoft.com/en-us/previo ... ottlenecks


_______________________________________


pus acum 10 zile
Site  
Pagini: 1    
Mergi la