Mrrrr's Forum (VIEW ONLY)
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 (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
Kalifa
 | Femeie
19 ani
Ialomita
cauta Barbat
19 - 32 ani |
|
|
TRaP
Moderator
Inregistrat: acum 6 ani
Postari: 748
|
|
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:
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 5 ani)
|
|
| pus acum 5 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2186
|
|
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:
_______________________________________
|
|
| pus acum 4 ani |
|