PDA

View Full Version : Filter Rows with Duplicate values.



adamsm
06-27-2010, 06:02 AM
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
06-27-2010, 10:17 AM
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
06-27-2010, 11:26 AM
Add a helper column which will show the duplicates and filter on that.

adamsm
06-28-2010, 02:23 AM
Thanks for the reply mdmackillop.

If I may ask, how could that be done.

Thanks in advance

Bob Phillips
06-28-2010, 02:58 AM
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

mdmackillop
06-28-2010, 03:03 AM
Example

adamsm
06-28-2010, 06:06 AM
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
06-28-2010, 06:20 AM
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
06-28-2010, 06:32 AM
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
06-28-2010, 06:37 AM
Private Sub cmdDelete_Click()
Call DeleteAndClearFilter
Cells.Rows.Hidden = False
Call FilterList
End Sub

adamsm
06-28-2010, 06:44 AM
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
06-28-2010, 06:50 AM
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
06-28-2010, 08:02 AM
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
06-28-2010, 08:37 AM
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
06-28-2010, 08:38 AM
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.