AdvancedFilter displaying Unique values.

[Kevin@Radstock]

Hi All

Just joined the forum at the back end of 2015, looking forward to learning from you guys. I am more of a formula guy but I have just got into the coding side.

Attached is a file with a sub procedure using the AdvancedFilter method to filter unique values. But as you can see in the attached file it is incorrect in column C as the name Kevin has come up twice. Am I missing something!

Kevin

[snb]

If you remove the named ranges first, this will do:


Sub M_snb()
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, , Sheet1.Cells(1, 3), -1
End Sub

[Kevin@Radstock]

Hi snb

That does not work for me, even if I copy your code into a new work book. But the book you uploaded works. Could this be a region issue ie. I'm UK based?

[snb]

No, as I told you: it's a 'named range' issue. Remove/delete all named ranges and run the code.

Besides you should always avoid to give your macros, variable. named ranges names that have a certain role/function in VBA or Excel.
Preferably you always use underscores in the names you create yourself. macros M_snb(), function F_snb, variables a_sn, t_c01, named ranges R_snb etc.

[Kevin@Radstock]

snb

Thanks for that, can you explain the red highlighted parts of the code.

Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, , Sheet1.Cells(1, 3), -1

[Kevin@Radstock]

Well after a bit of searching around, I seem to have found a solution. But I could still do with an explanation to your coding snb. Thanks


Sub Filter_Data()

With Sheet1.Range("A1", Range("A1").End(xlDown))

.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("C1"), Unique:=True
.Range("C1") = "Uniques"


End With


End Sub

[snb]

Yes,

Goto VBeditor: Alt F1
Open Help: F1
type: advancedfilter & Enter
read what I can't explain better than the developers.


.Range("C1") = "Uniques"
Looks as if you didn't read my suggestions.

[Kevin@Radstock]

.Range("C1") = "Uniques"
Looks as if you didn't read my suggestions.

What do you mean, I am renaming the header am I not, or is that a problem!

[snb]

No, I misread your post. I'm sorry. Forget my last remark in my last post.

But I keep advising you to use:

Sheet1.Cells(1).CurrentRegion