Filter Rows with Duplicate values.

[adamsm]

Hi,

The following code deletes the duplicate rows from the worksheet.

How could I change it so that it filters the duplicate rows instead of deleting.

Sub FilterDups()
Dim x               As Long
Dim LastRow         As Long
    LastRow = Range("A1048756").End(xlUp).Row
    For x = LastRow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
            Range("A" & x).EntireRow.Delete
        End If
    Next x
End Sub

Any help on this would be kindly appreciated.

[adamsm]

I did try by adding the following line
Dim rng As Range
Set rng = ActiveSheet.AutoFilter.Range
But it was of no hope. and it didn't make any sense.

[mdmackillop]

Add a helper column which will show the duplicates and filter on that.

[adamsm]

Thanks for the reply mdmackillop.

If I may ask, how could that be done.

Thanks in advance

[Bob Phillips]

[vba]

Sub FilterDups()
Dim LastRow As Long
Dim rng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row
Columns(1).Insert
Range("A1").Value2 = "Filter"
Range("A2").Resize(LastRow - 1).Formula = "=COUNTIF($B$1:$B$" & LastRow & ",B2)>1"
Set rng = Range("A1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="TRUE"
End Sub
[/vba]

[mdmackillop]

Example

[adamsm]

Thanks for the reply & help mdmackillop & xld.

To understand my problem better I have attached a sample workbook.

The present code only hides the unique rows displaying the duplicate rows with the macro code FilterAllDuplicateRows(). And the duplicate rows get deleted with the macro DeleteAndClearFilter()

What I’m trying to get help is to display the data in the list box, as the sheet hides all the unique rows displaying the duplicate rows when the command button Search Dup is clicked. And also display the data in the list box when macro deletes the duplicate rows and Unhides the unique rows on click of the command button Del Dups.

In short, how do I filter and unfilter the rows simultaneously from the list box and the sheet with the command buttons?

[Bob Phillips]

Let me get this right. Do you want to only show unqiue values in the lastbox, or do you only want to show each item only once in the listbox, or something else?

And then what?

[adamsm]

Thanks for the reply Xld. Here's a better version.

In this version when the user clicks the search dup button the listbox and the worksheet shows only the duplicate rows.

This is fine with me.

When the command button del dup is clicked the code deletes the dup rows and unhides the unique rows.

but this unhiding is not seen in the listbox. When the worksheet unhides the rows I'm trying to show all the unhidden rows on the listbox simulatenously with the worksheet.

I hope I have made my question clear.

[Bob Phillips]

[vba]

Private Sub cmdDelete_Click()
Call DeleteAndClearFilter
Cells.Rows.Hidden = False
Call FilterList
End Sub
[/vba]

[adamsm]

Thanks for the code xld.

It seems that the following code also does delete & unhide the rows both form the worksheet & the Listbox.

Private Sub cmdDelete_Click()
    Dim x               As Long
    Dim LastRow         As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For x = LastRow To 6 Step -1
    If Application.WorksheetFunction.CountIf(Range("A6:A" & x), Range("A" & x).Text) > 1 Then
    Range("A" & x).EntireRow.Delete
        End If
    Next x
    Range("A6", "A" & LastRow).EntireRow.Hidden = False
    FilterList
End Sub

[adamsm]

Meanwhile how could I display a message like "from a total of 15 rows 4 duplicate entries were found" in the userform title bar with the click of the search dups button( if duplicate rows were found). And if there are no duplicate values "no duplicate values found".

[mdmackillop]

This is a good example of "Project Creep". Please be clear from the outset what your requirements are. I suspect there is a lot of wasted time and effort here.

[adamsm]

mdmackillop, if you label what I had asked as "a good example of Project Creep"; Nevermind. I'll try to figure out what it for myself. Meanwhile, for the rest of the forum users I guess my workbook would give some help to assist them in dealing with duplicate data entries.

Anyways, Once again thanks for all the help & support that had been given me in this thread.

[adamsm]

Once the problem is solved Ill try to post the completed workbook.
Anyways, Once again thanks for all the help & support that had been given me in this thread.