Stepping throu advanced filter visible cells = FAIL

[Puffin617]

I used advanced filter to create a filtered list of unique values in my 3rd column. Now I want to step through them to read them all into an array.

My problem is that is starts off on row 1, skipps row 2 & 3 and continues from 4 correctly...

Please explain this "Area" thing to me:

Sub Macro5()
Dim QCNameArray(1 To 50) As String

    Columns(3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Counter = 1
    
    Do While ActiveCell.Value <> ""
        
        ActiveSheet.Columns(3).SpecialCells(xlCellTypeVisible).Areas(Counter).Rows(1).Select
        
        QCNameArray(Counter) = ActiveCell.Value
        Counter = Counter + 1
    Loop
    ActiveSheet.ShowAllData
    
End Sub

 

[Puffin617]

bump

 

[Weaver]

I don't know if you need me to write the code, but I'd be inclined to filter the data to another location, then you don't have the worry of whether cells are invisible or not.

 

[Weaver]

Alternatively, use this function

Function unique(ByVal rng As Range) As Variant
    Dim c, v
    With CreateObject("scripting.dictionary")
        For Each c In rng
            v = c.Value
            If Not .exists(v) Then .Add v, 1
        Next c
        unique = WorksheetFunction.Transpose(.keys)
    End With
End Function

If you pass it a range, it will return an array comprised of all the unique values in the range.

 

[Puffin617]

This Function seems interesting - what exactly does it do?

 

[Puffin617]

I don't understand how to use this function - can someone please tell me how to use it?

 

[RoryA]

If you have already produced a unique filter, then all you need to do is loop through all the cells:

Sub Macro5()
Dim QCNameArray(1 To 50) As String

    Columns(3).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    Counter = 1
    
    For each rngcell in ActiveSheet.Columns(3).SpecialCells(xlCellTypeVisible)
        
        QCNameArray(Counter) = rngCell.Value
        Counter = Counter + 1
    next rngcell
    ActiveSheet.ShowAllData
    
End Sub

 

[Weaver]

I don't understand how to use this function - can someone please tell me how to use it?

Call it from your code

myArray = unique(myRange)