Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unique Item List still contains duplicates
Hello,
I tried to post earlier and received an error. So if this shows up as a duplicate post, my apologies. I have a list of records that contains duplicate item numbers in one column (which is ok in this setup). I would like to filter this list for unique items and display in another sheet of the same wb. I have given the column that I want to filter a dynamic named range with the following formula: my_range=OFFSET(Sheet1!$H$1,1,0,COUNTA(Sheet1!$H:$ H)) The VBA code I am using (which I found on Ozgrid and modified some) is listed below. What is happening right now is that everything is copied and sorted just fine except for the very first record in the original list. That particular number appears at the top of the filtered and sorted list, whether it would be in order there or not and that same number also shows up in the correct spot of the filtered and sorted list. I hope this makes sense. Here is the code: Private Sub Cmd_UniqueList_Click() Dim rListSort As Range, rOldList As Range Dim strListFill As String 'Clear Hidden sheet Column A ready for list Sheet1.Range("B2", Sheet1.Range("B65536").End(xlUp)).Clear Sheet2.Activate 'Set range variable to list we want Set rOldList = Sheet2.Range("Order_ItemNumber") 'Use AdvancedFilter to copy the list to Column N of the hidden sheet and remove all dupes rOldList.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet1.Cells(2, 2), Unique:=True 'Set range variable to the new non dupe list Set rListSort = Sheet1.Range("B2", Sheet1.Range("B65536").End(xlUp)) 'Sort the new non dupe list With rListSort .Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With strListFill = Sheet1.Name & "!" & Sheet1.Range("B2", Sheet1.Range("B65536").End(xlUp)).Address End Sub All suggestions and solutions are greatly appreciated. Thanks in advance. Regards, A. Crawford |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I delete unique numbers from a list and save duplicates? | Excel Worksheet Functions | |||
unique item remover | Excel Discussion (Misc queries) | |||
Unique item list still returns duplicates | Excel Programming | |||
Array Formulas - Unique List from List with Duplicates | Excel Discussion (Misc queries) | |||
Add Unique item to Collection | Excel Programming |