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

The advanced filter (which is used in the code) assumes the first row
contains column headings. So it treats the first row as a column heading and
then gets a unique list. This will cause the single duplicate entry you
describe. Put column headings/labels in row1 (moving your list down 1 row)
and it will work.

Or add a line in your code to delete the first row of the unique list
although this might be problematic if the original data's first row is
unique. .

--
Regards,
Tom Ogilvy


"AC" wrote:

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