Delete & Unfilter

[HansV]

I myself would leave it that way, because n is the number of items left in the list box after filtering. Displaying another number could cause confusion. But you could change n to (n - 1).

[adam]

Hans I guess now could figure out the problem. Its because the line counts the number of visible rows. Since the code counts the visible rows it telling that two duplicate rows are found even if one duplicate row exists.
the code behind the workbookhere shows only one row of duplicate data and for this reason it says that only one duplicate row found.

Even changing n to (n - 1) does not make any difference.

[HansV]

It seems reasonable to me to count the number of visible rows. If you don't like that, you can try to modify the code from the other post to suit your purpose; I'm not going to do that for you.

[adam]

I never told that I didn't like having the code to count the number of visible rows. What I told was that I was able to figure out the reason why I'm not getting what I had asked at Post 20692. So I hope you would make no mistake about that.

Anyway,

Suppose I’m having the user form title bar caption as UserForm1 on initialization.
After clicking the button (SearchDup); if there is no duplicate values, the title bar caption shows as “No duplicate Values Found! And it stays on, until the user form is unloaded.
Instead of doing this, how could I change the caption back again to userform1.
Let’s say the message is displayed for 2 seconds and then again the caption UserForm1 gets displayed automatically.

[HansV]

Perhaps it'd be better to display a MsgBox instead of changing the caption of the form.

[adam]

I agree with your suggestion Hans.

The disadvantage behind the message box is that if the message box is displayed I cannot scroll through the list box to see the rows with duplicate entries if the database is large.

Code: Select all

Sub FilterDuplicateRows()
  Dim x               As Long
  Dim LastRow         As Long
  Dim n               As Long
  Application.ScreenUpdating = False
  LastRow = Range("A" & Rows.Count).End(xlUp).Row
  For x = LastRow To 6 Step -1
    If Application.WorksheetFunction.CountIf(Range("A6:A" & LastRow), Range("A" & x).Text) = 1 Then
      Range("A" & x).EntireRow.Hidden = True
    Else
      n = n + 1
    End If
  Next x
  If n = 0 Then
     MsgBox "No Duplicate Values Found"
     Range("A6", "A" & LastRow).EntireRow.Hidden = False
  Else
    caution = MsgBox(n & " Duplicate Entries Among " & (LastRow - 5) & " Entries Found.")
  End If
  Application.ScreenUpdating = True
  FilterList
End Sub

The above modification does not create a message box with the number of duplicate rows.

If I'm wrong how should I display the duplicate row message in a message box?

Any suggestion would be appreciated.

[adam]

Instead of caution = MsgBox(n & " Duplicate Entries Among " & (LastRow - 5) & " Entries Found.")
I've used Me.Label1 = n & " Duplicate Entries Among " & (LastRow - 5) & " Entries Found." which is much better. The label is hidden during initialization and gets visible when the command button SearchDup is clicked. Anyways, thanks for the suggestion Hans.

[adam]

Following is the code which I'm using to display the deleted rows on a label. But the message that I'm getting on the label is the opposite of what Im wanting.
Meaning if two rows are being deleted it says that 12 rows are being deleted meaning it refers to the rows that aren't deleted.

Where have I gone wrong here? I believe I have placed the lines that display the deleted rows information on a wrong location.

Code: Select all

Private Sub cmdDelete_Click()
    Dim x               As Long
    Dim LastRow         As Long
    Dim n               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
    Else
    n = n + 1
    End If
    Me.Label2 = n & " Duplicate Entries Among " & (LastRow - 5) & " Entries Deleted."
    Next x
    Range("A6", "A" & LastRow).EntireRow.Hidden = False
    FilterList
    Label2.Visible = True
End Sub

[HansV]

You're increasing the value of n if you DON'T delete a row. You should increase it if you DO delete a row.
And there is no need to set the caption of the label within the For ... Next loop; you can do so after the loop.

[adam]

Thanks for the reply Hans. Now the code works fine.

The following code deletes the selected (visible) row from the list box. But does not unhide the rows

I’m trying to figure out a way how to make this code to delete the selected row and unhide the hidden rows from both the list box and the worksheet simultaneously.

Code: Select all

  Private Sub cmdRemoveDup_Click()
    Dim i As Long
    i = ListBox2.ListIndex
    With Range("Data").SpecialCells(xlCellTypeVisible)
        .Rows.EntireRow(i + 1).Delete
    End With
   FilterList
End Sub

I would be happy if you could give me a suggestion to do so?!!

[HansV]

You can use Rows.Hidden = False to unhide all rows.

[adam]

Thanks for the reply Hans. It works fine if the Multi Select property of the list box is set to 0-fmMultiselect single. But If the Multi Select property of the list box is set to 1-fmMultiselect Multi and If I try to delete two rows, it doesn't work. How could I overcome this?

[HansV]

Try

Code: Select all

Private Sub cmdRemoveDup_Click()
    Dim i As Long
    For i = 0 To Me.ListBox2.ListCount - 1
      If Me.ListBox2.Selected(i) Then
        With Range("Data").SpecialCells(xlCellTypeVisible)
          .Rows.EntireRow(i + 1).Delete
        End With
     End If
  Next i
  FilterList
End Sub

[adam]

Thanks for the reply Hans.

In the attached workbook when the command button search duplicates is clicked, the list box filters rows with duplicate data.

Among them if the user selects any two rows and hit the macro delete selected dups. The code does not delete all the rows that are being selected by the user.

How may I overcome this?

[HansV]

Try this version:

Code: Select all

Private Sub CommandButton_Remove_Click()
  Dim i As Long
  For i = Me.ListBox2.ListCount - 1 To 0 Step -1
    If Me.ListBox2.Selected(i) Then
      With Range("Data").SpecialCells(xlCellTypeVisible)
        .Rows(i + 1).Delete
      End With
    End If
  Next i
  FilterList
End Sub

[adam]

Thanks for the reply & help Hans.

How do I unhide the rows after deleteing the selected rows.

I did give it a try by adding the following lines but it gives me the debug message.

Code: Select all

Private Sub CommandButton_Remove_Click()
  Dim i As Long
  Dim LastRow         As Long
  For i = Me.ListBox2.ListCount - 1 To 0 Step -1
    If Me.ListBox2.Selected(i) Then
      With Range("Data").SpecialCells(xlCellTypeVisible)
        .Rows(i + 1).Delete
      End With
    End If
  Next i
  Range("A6", "A" & LastRow).EntireRow.Hidden = False
  FilterList
End Sub

What have I done wrong here?

[HansV]

Change

Range("A6", "A" & LastRow).EntireRow.Hidden = False

to

Range("A6:A" & LastRow).EntireRow.Hidden = False

[adam]

Thanks for the suggestion Hans. But the debug message still exists highlighting the line

Range("A6:A" & LastRow).EntireRow.Hidden = False

[HansV]

It would help if you assigned a value to LastRow.

[adam]

I've added the following line
LastRow = Cells(Rows.Count, "A").End(xlUp).Row

above
Range("A6:A" & LastRow).EntireRow.Hidden = False .

But it does not work. What may be wrong here?