Identifying the number of unique values in a column

[PipBoy808]

I have an array of data (D6:O36). The data is arranged by day of the week. These are listed in column G and the 31 rows are listed over 5 days (Saturday-Wednesday).

I'm looking for a way that I can tell Excel to look through G:G (the total number of rows needs to be dynamic) and return the number of unique days, which in this case is 5, so that I can use this value later on in some other code.

Hard coding this isn't an option given that the data needs to be dynamic. It's always so complicated ...

 

[Luke M]

Here's a handy macro I have in my Personal.xls file, for generating Unique lists.

Sub GetUniqueList()
'Ctrl+Alt+u
 
    Dim rCell As Range
    Dim colUnique As Collection
    Dim sh As Worksheet
    Dim i As Long
    
    'only work on ranges
   If TypeName(Selection) = "Range" Then
        
        'create a new collection
       Set colUnique = New Collection
        
        'loop through all selected cells
       'and add to collection
       For Each rCell In Selection.Cells
            On Error Resume Next
                'if value exists, it won't be added
               colUnique.Add rCell.Value, CStr(rCell.Value)
            On Error GoTo 0
        Next rCell
        
        'make a new sheet to put the unique list
       Set sh = ActiveWorkbook.Worksheets.Add
        
        'Write the unique list to the new sheet
       For i = 1 To colUnique.Count
            sh.Range("A1").Offset(i, 0).Value = colUnique(i)
        Next i
        
        'sort with no headers
       sh.Range(sh.Range("A2"), sh.Range("A2").End(xlDown)) _
            .Sort sh.Range("A2"), xlAscending, , , , , , xlNo
        
    End If
    
End Sub

You should be able to take the top portion, where it loops through rCell, and then use colUnique.Count to give you the value you need in your code. The code above obviously needs to be tweaked a little to meet your situation, but it should be a good starting point.

 

[PipBoy808]

Works like a charm. Thanks!

 

[PipBoy808]

Or rather, it almost works like a charm. Excel is counting "" as a distinct value and therefore returning a value of 6 for the collection instead of 5. Is there any way I could get it to not count blanks?

EDIT: Sorted it myself by using a lastrow variable. Thanks a lot. It works perfectly.