Advertisement
Guest User

Untitled

a guest
Jun 20th, 2019
71
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.26 KB | None | 0 0
  1. Sub GetFilteredColumn()
  2.  
  3. Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet1")
  4. Dim iLRow As Long, iRow As Long
  5. Dim aFilTags As Variant
  6. Dim oKey As Variant
  7. Dim oDic As New Dictionary
  8.  
  9. With oWS
  10.  
  11. ' Get row count of visible rows
  12. iLRow = .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
  13.  
  14. ' Check if any rows were returned after the filter
  15. If iLRow > 1 Then
  16. ' Get column 8 of the filtered range into an array
  17. ' ** THIS is where i'm trying to capture column 8 into and array **
  18. 'aFilTags = .AutoFilter.Range
  19. 'aFilTags = .AutoFilter.Range.Columns(8).SpecialCells(xlCellTypeVisible).Rows
  20. aFilTags = .Columns(8).SpecialCells(xlCellTypeVisible)
  21.  
  22. ' Get unique values in dictionary
  23. For iRow = 2 To UBound(aFilTags)
  24. If Not oDic.Exists(aFilTags(iRow, 1)) Then
  25. oDic.Add aFilTags(iRow, 1), aFilTags(iRow, 1)
  26. End If
  27. Next
  28.  
  29. ' Display the unique list
  30. iRow = 0
  31. For Each oKey In oDic.Keys
  32. iRow = iRow + 1
  33. .Range("AZ" & iRow).Value = oDic(oKey)
  34. Next
  35.  
  36. End If
  37.  
  38. End With
  39.  
  40. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement