Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub GetFilteredColumn()
- Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet1")
- Dim iLRow As Long, iRow As Long
- Dim aFilTags As Variant
- Dim oKey As Variant
- Dim oDic As New Dictionary
- With oWS
- ' Get row count of visible rows
- iLRow = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
- ' Check if any rows were returned after the filter
- If iLRow > 1 Then
- ' Get column 8 of the filtered range into an array
- ' ** THIS is where i'm trying to capture column 8 into and array **
- 'aFilTags = .AutoFilter.Range
- 'aFilTags = .AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Rows
- aFilTags = .Columns(8).SpecialCells(xlCellTypeVisible)
- ' Get unique values in dictionary
- For iRow = 2 To UBound(aFilTags)
- If Not oDic.Exists(aFilTags(iRow, 1)) Then
- oDic.Add aFilTags(iRow, 1), aFilTags(iRow, 1)
- End If
- Next
- ' Display the unique list
- iRow = 0
- For Each oKey In oDic.Keys
- iRow = iRow + 1
- .Range("AZ" & iRow).Value = oDic(oKey)
- Next
- End If
- End With
- End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement