I am trying to create a dashboard where:
- It takes column A and creates an array of filtered names (autofilter) based off columns C, D, or E
- C, D, and E only have Yes or No
- I will only use one of the columns (C,D,E) to filter the names.
- I then use the stored array to manipulate data.
The problem I am running into though is that when I use the autofilter feature and then use my code, I am unable to capture just the filtered data along with the last row.
So far I have been manually filtering the data and then testing my code.
Sub tester()
Dim LastRow As Integer
LastRow = ActiveWorkbook.Sheets("employees").UsedRange.Rows.count
vasWksNames = UniquesFromRange(ActiveWorkbook.Sheets("employees").Range("A2:A" & LastRow))
If UBound(arr) = -1 Then
Debug.Print "no values found"
Else
Debug.Print "got array of unique values"
End If
End Sub
Function UniquesFromRange(rng As Range)
Dim d As Object, c As Range, tmp
Set d = CreateObject("scripting.dictionary")
For Each c In rng.Cells
tmp = Trim(c.Value)
If Len(tmp) > 0 Then
If Not d.Exists(tmp) Then d.Add tmp, 1
End If
Next c
UniquesFromRange = d.keys
End Function
Unfiltered data -> Filtered data -> Create Array
Anything helps!
>Solution :
SpecialCells(xlCellTypeVisible) gets all visible cells.
Please try.
Function UniquesFromRange(rng As Range)
Dim d As Object, c As Range, tmp
Set d = CreateObject("scripting.dictionary")
For Each c In rng.SpecialCells(xlCellTypeVisible).Cells
tmp = Trim(c.Value)
If Len(tmp) > 0 Then
If Not d.Exists(tmp) Then d.Add tmp, 1
End If
Next c
UniquesFromRange = d.keys
End Function
Please refers to Microsoft document.