5 thoughts on “Unique Values Advanced Filter”

1. quote You can only copy filtered data to the active sheet.
   unquote

   not necessary Debra Dalgleish has a solution in
   http://www.contextures.com/xladvfilter01.tml#ExtractWs
   I have made some notes on this webpage so that I and my brother can understand implia;tions easily -given below
   quote
   you make sheet 2 as activesheet. choose an empty cell in sheet2. then click data-advance filter. the data range and criteris range you go to sheet1 and choose the ranges. everytime you finish selecing it will come back to sheet2 because that is the activesheet.
   now click and now choose any cell in sheet2
   the filtered data is copied to sheet2 though database and criteria are in sheet1.
   unquote

2. I tried to copy filtered data on another sheet but it did not work, though it did work for only active worksheet.

3. Here is some code that I use to get a list of unique items. Becuase I have to do it so often it is a pain to have to filter… This is a attached to a menu item that I have on a custom menu bar.

   Private Sub GetUniqueItems()
   Dim cell As Range ‘Current cell in range to check
   Dim rngToSearch As Range ‘Cells to be searched
   Dim dic As Scripting.Dictionary ‘Dictionary Object
   Dim dicItem As Variant ‘Items within dictionary object
   Dim wks As Worksheet ‘Worksheet to populate with unique items
   Dim rngPaste As Range ‘Cells where unique items are placed

   ‘Create range to be searched
   Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)

   ‘Confirm there is a relevant range selected
   If Not rngToSearch Is Nothing Then
   ‘Create dictionay object
   Set dic = New Scripting.Dictionary

   ‘Populate dictionary object with unique items (use key to define unique)
   For Each cell In rngToSearch ‘Traverse selected range
   If Not dic.Exists(cell.Value) And cell.Value Empty Then ‘Check the key
   dic.Add cell.Value, cell.Value ‘Add the item if unique
   End If
   Next

   If Not dic Is Nothing Then ‘Check for dictionary
   Set wks = Worksheets.Add ‘Create worksheet to populate
   Set rngPaste = wks.Range(“A1?) ‘Create range to populate
   For Each dicItem In dic.Items ‘Loop through dictionary
   rngPaste.NumberFormat = “@” ‘Format cell as text
   rngPaste.Value = dicItem ‘Add items to new sheet
   Set rngPaste = rngPaste.Offset(1, 0) ‘Increment paste range
   Next dicItem
   ‘Clean up objects
   Set wks = Nothing
   Set rngPaste = Nothing
   Set dic = Nothing
   End If
   End If
   End Sub

   You need to reference the Microsoft Scripting Runtime to use this…

4. Mr John Walkenbach has also offered a possible solution for working with unique items.

   Try http://j-walk.com/ss/excel/tips/tip15.htm

   It is a UDF called UniqueItems (starting at example 4). Since I use it quite often I have exchanged UniqueItems for just Unique and vice versa to reduce the typing involved. I hope this helps.

5. Is there an inbuilt excel function in Excel in get unique set of values from a range?

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.