VBA Not deleting the correct row/ duplicate names

[SHUTTEHFACE]

Hi,

I've been using the following code to delete the row of a person who's name is selected from a combo box. It has been working great until recently where names can be duplicated in the list. As a result, When I select a name it is deleting someone else's row. Any suggestions as to how to fix this?

Private Sub CommandButton2_Click()

Sheets("PS Scheduler").Range("PSList").Cells(cmbPS.ListIndex + 1, 1).EntireRow.Delete


Dim MyUniqueList As Variant, i As Long
    With Me.cmbPS
        .Clear ' clear the combobox content
        MyUniqueList = UniqueItemList(Range("PSList"), True)
        For i = 1 To UBound(MyUniqueList)
            .AddItem MyUniqueList(i)
        Next i
        .ListIndex = 0 ' select the first item
    End With


End Sub

The second part of the code calls on a function I have to display a list of unique names without duplicates in the combo box.

Thanks!

M

[Leith Ross]

Hello SUTTEHFACE,

Can you post your workbook?

Posting your workbook will get you answers more quickly and all solutions can be tested directly.

Please provide before and after examples of your data along with any notes you would like to include.

If your workbook contains any sensitive information, please redact that information before you post your workbook.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[SHUTTEHFACE]

Schedule EX.xlsm

Sorry for the late reply,

Attached is a copy of my workbook. As you will see, when I try to delete someone, it deletes the wrong person. I commented out some of the VBA code I was working on recently, the one still in use is what I was using before I started having duplicates in column A.

Thanks,

M

[jaslake]

Hi SUTTRHFACE

Replace your Command Button 2 Code with this

Private Sub CommandButton2_Click()
  Dim Rng As Range
  Dim ws As Worksheet
  Dim LR As Long, x As Long

  Set ws = Sheets("PS Scheduler")
  Application.ScreenUpdating = False
  With ws
    LR = .Range("A" & .Rows.Count).End(xlUp).Row

    If Not .AutoFilterMode Then
      .Rows("6:6").AutoFilter
    End If
    .Range("A6:E" & LR).AutoFilter Field:=1, Criteria1:=Me.cmbPS.Value
    Set Rng = .AutoFilter.Range
    x = Rng.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    If x >= 1 Then
      .Range("A7:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    .AutoFilterMode = False
  End With
  Application.ScreenUpdating = True
End Sub

[SHUTTEHFACE]

This seems to work great! Thank you for your help

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.