Unique item list still returns duplicates
 

Hello,
I have a list in Excel that contains records with duplicate numbers. I
am trying to filter this list via VBA to retrieve only unique records
and place that list on another sheet in the same wb.

I have given the column that contains the duplicate numbers a dynamic
named range with the following formula:
dub_range=OFFSET($A$1,0,0,COUNTA($A:$A))

The VBA code, which I found on ozgrid is listed below. When I run this
code it seems to work, except that for some reason the very first item
in the list is returned at the very top of my filtered list,
regardless of where it would normally appear in a sorted list and it
also appears in the list where it normally should appear in a sorted
list.

Sub UniqueList()
Dim rListSort As Range, rOldList As Range
Dim strListFill As String

'Clear Hidden sheet Column A ready for list
Sheet8.Range("N1", Sheet8.Range("N65536").End(xlUp)).Clear


'Set range variable to list we want
Set rOldList = Sheet2.Range("PO_Number")

'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet8.Cells(1, 14), Unique:=True

'Set range variable to the new non dupe list
Set rListSort = Sheet8.Range("N1",
Sheet8.Range("N65536").End(xlUp))

'Sort the new non dupe list
With rListSort
.Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End With

strListFill = Sheet8.Name & "!" & Sheet8.Range _
("N1", Sheet8.Range("N65536").End(xlUp)).Address

Sheet8.cbo_PO.ListFillRange = vbNullString
Sheet8.cbo_PO.ListFillRange = strListFill
End Sub


All help is greatly appreciated.

Thanks in advance.
Regards,
A. Crawford

Unique item list still returns duplicates
 

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 have a list in Excel that contains records with duplicate numbers. I
am trying to filter this list via VBA to retrieve only unique records
and place that list on another sheet in the same wb.

I have given the column that contains the duplicate numbers a dynamic
named range with the following formula:
dub_range=OFFSET($A$1,0,0,COUNTA($A:$A))

The VBA code, which I found on ozgrid is listed below. When I run this
code it seems to work, except that for some reason the very first item
in the list is returned at the very top of my filtered list,
regardless of where it would normally appear in a sorted list and it
also appears in the list where it normally should appear in a sorted
list.

Sub UniqueList()
Dim rListSort As Range, rOldList As Range
Dim strListFill As String

'Clear Hidden sheet Column A ready for list
Sheet8.Range("N1", Sheet8.Range("N65536").End(xlUp)).Clear


'Set range variable to list we want
Set rOldList = Sheet2.Range("PO_Number")

'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet8.Cells(1, 14), Unique:=True

'Set range variable to the new non dupe list
Set rListSort = Sheet8.Range("N1",
Sheet8.Range("N65536").End(xlUp))

'Sort the new non dupe list
With rListSort
.Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End With

strListFill = Sheet8.Name & "!" & Sheet8.Range _
("N1", Sheet8.Range("N65536").End(xlUp)).Address

Sheet8.cbo_PO.ListFillRange = vbNullString
Sheet8.cbo_PO.ListFillRange = strListFill
End Sub


All help is greatly appreciated.

Thanks in advance.
Regards,
A. Crawford

Unique item list still returns duplicates
 

It is looking for a header since you told it to guess. Try using
Header:=xlNone

"AC" wrote:

Hello,
I have a list in Excel that contains records with duplicate numbers. I
am trying to filter this list via VBA to retrieve only unique records
and place that list on another sheet in the same wb.

I have given the column that contains the duplicate numbers a dynamic
named range with the following formula:
dub_range=OFFSET($A$1,0,0,COUNTA($A:$A))

The VBA code, which I found on ozgrid is listed below. When I run this
code it seems to work, except that for some reason the very first item
in the list is returned at the very top of my filtered list,
regardless of where it would normally appear in a sorted list and it
also appears in the list where it normally should appear in a sorted
list.

Sub UniqueList()
Dim rListSort As Range, rOldList As Range
Dim strListFill As String

'Clear Hidden sheet Column A ready for list
Sheet8.Range("N1", Sheet8.Range("N65536").End(xlUp)).Clear


'Set range variable to list we want
Set rOldList = Sheet2.Range("PO_Number")

'Use AdvancedFilter to copy the list to Column A _
of the hidden sheet and remove all dupes
rOldList.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Sheet8.Cells(1, 14), Unique:=True

'Set range variable to the new non dupe list
Set rListSort = Sheet8.Range("N1",
Sheet8.Range("N65536").End(xlUp))

'Sort the new non dupe list
With rListSort
.Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
End With

strListFill = Sheet8.Name & "!" & Sheet8.Range _
("N1", Sheet8.Range("N65536").End(xlUp)).Address

Sheet8.cbo_PO.ListFillRange = vbNullString
Sheet8.cbo_PO.ListFillRange = strListFill
End Sub


All help is greatly appreciated.

Thanks in advance.
Regards,
A. Crawford