Not understanding Advanced Filter Unique

[Tim Hanson]

I have a Advanced Filter Macro that copies Unique rows to a new sheet.

But it is not doing as I expect.

I want to move all rows where cells in a column are unique to that end I have set: CriteriaRange:=ws.Range("F2:F" & lRow)

But the macro is moving all rows that are unique only if every cell in a row is unique from all cells in any other row (I hope that make sense)

Do I misunderstand stand what CriteriaRange means?

Am I misusing Advanced Filter?

Thanks

Sub Filter()
  Dim ws As Excel.Worksheet
  Dim r As Range, r1 As Range
  Dim lRow As Long

  Set ws = ThisWorkbook.Sheets("UnPivot")
      lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
     
  'Sort on column
  Set r1 = ws.Range("F1:F" & lRow)
      r1.Sort Key1:=r1, Order1:=xlAscending, Header:=xlYes
     
  'Filter Unique True
  Set r = ws.UsedRange
      r.AdvancedFilter _
      CriteriaRange:=ws.Range("F2:F" & lRow), _
      Action:=xlFilterCopy, _
                      CopyToRange:=ThisWorkbook.Sheets("XXX").Range("A1"), _
                                                              unique:=True

End Sub

 

[Luke M]

Misunderstanding of the Criteria Range. That range is where you would list what you want to filter source data by.
http://www.contextures.com/xladvfilter01.html#setupcriteria

I'm not sure what you want the final output to look like. If you only want the unique values from col F, then you would do the Advanced Filter just on that range.

Range("F2:F" & lRow).AdvancedFilter Action:=xlFilterCopy, _
                      CopyToRange:=ThisWorkbook.Sheets("XXX").Range("A1"), unique:=True

 

[Tim Hanson]

Hi Luke, thanks for the comments and link, they were helpful.

I was wanting that if a cell in column F was unique it would copy the entire row of which that cell is a member to the new sheet

But I think now that AF is not the way to do this

 

[Hui]

Tim

In Advanced Filter "Unique" means the following

If your data is A, A, B, C, C, D
Unique in Advanced Filter unique means A, B, C, D

So I would add another field to your Data "Count"
In that field put A formula something like =Countif(F2,F2:F20) or whatever the bottom of your data range is
Then as part of the macro Filter the data to only show Records of Count = 1, the unique records

 

[NARAYANK991]

Hi Tim ,

I suggest you first try to do manually what you wish to do using code.

If you click on the Advanced Filter button , you can see that the text associated with the checkbox is :

Unique Records only

What this means is that only if all the column cells are the same in 2 rows , will the two rows be called duplicate records. What you wish to do can be done in a different way.

Do this manually to get a feel of how it works , and then write the code.

1. Filter your list in place
2. Select your data range in column F
3. Select your criteria range as the same range as in step 2
4. Check the checkbox labelled Unique Records only
5. OK

The display will now show only the unique records , while all duplicate rows will be hidden.

If this is what you want , you can code this , and then do a SpecialCells(xlCellTypeVisible) to select all visible rows , and copy + paste these on a new sheet.

Narayan

 

[Tim Hanson]

Hui and Narayan, thank you both, I know understand better, I am going to experiment with both techniques.

 

[Tim Hanson]

I want to remove ALL Unique Rows leaving only rows that are duplicates
Using the advice given above I came up with the following, it has a problem in that it removes one duplicate of everything e.g

If there are 4 cells with xyz before filtering and deleting then after filtering and deleting there are only 3 cells

Is there a way to fix this

Thank you

Sub RemoveUnique2()
  Dim ws As Excel.Worksheet
  Dim lRow As Long

  Set ws = ThisWorkbook.Sheets("UnPivot")
      lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
  
    With ws
        .Range("F2:F" & lRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        .Range("F2:F" & lRow), Unique:=True
        .UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData
        'Sort on column
         .Range("F1:F" & lRow).Sort Key1:=.Range("F1:F" & lRow), Order1:=xlAscending, Header:=xlYes
    End With

End Sub

 

[Luke M]

To keep all duplicate rows:

1. Create helper column with formula:=COUNTIF(F:F,F2)>1
2. Filter helper column for False.
3. Delete all visible rows.

 

[Tim Hanson]

Luke, thanks for pointing that out, I should have with a little more thought about Hui and Narayan comments and what Make Unique is doing come up with that last bit on my own!