UserForm : Want to link Listbox1 with Listbox2 and delete data with one button

Hi, haroon2015,

I don´t see the necessity for 2 Listboxes with identical names as both lists could be handled in one ListBox. Due to the specifications you made the lists need to be identical as you refer to the position in the list and not to the name of the person (which would be more robust and allow different lists both on number of persons as of order of these). And to go into the future: no,I won´t deliver code for this sceanrio as you didn´t ask for it in the opening post.

Code behind the UserForm:

Option Explicit
Dim blnQuit As Boolean

Private Sub cmdDelete_Click()
  Dim lngIndex As Long
  Dim lngArray As Long
  Dim alDelete() As Long
  
  With Me.ListBox1
    For lngIndex = 1 To .ListCount
      If .Selected(lngIndex - 1) = True Then
        lngArray = lngArray + 1
        ReDim Preserve alDelete(lngArray)
        alDelete(lngArray) = lngIndex
      End If
    Next lngIndex
  End With
  
  If lngArray > 0 Then
    For lngIndex = UBound(alDelete) To 1 Step -1
      Range("DEL_UNQ_TO").Offset(alDelete(lngIndex), 0).Cells.Delete
      Range("DEL_UNQ9").Offset(alDelete(lngIndex), 0).Cells.Delete
    Next lngIndex
  End If
  
  Call UserForm_Activate
End Sub

Private Sub ListBox1_Change()
  Dim lngIndex As Long
  If blnQuit Then Exit Sub
  With Me.ListBox1
    blnQuit = True
    For lngIndex = 0 To .ListCount - 1
      If .Selected(lngIndex) Then
        Me.ListBox2.Selected(lngIndex) = True
      Else
        Me.ListBox2.Selected(lngIndex) = False
      End If
    Next lngIndex
    blnQuit = False
  End With
End Sub

Private Sub ListBox2_Change()
  Dim lngIndex As Long
  If blnQuit Then Exit Sub
  With Me.ListBox2
    blnQuit = True
    For lngIndex = 0 To .ListCount - 1
      If .Selected(lngIndex) Then
        Me.ListBox1.Selected(lngIndex) = True
      Else
        Me.ListBox1.Selected(lngIndex) = False
      End If
    Next lngIndex
    blnQuit = False
  End With
End Sub

Private Sub UserForm_Activate()
  Dim lngIndex As Long
  
  With Me.ListBox1
    .Clear
    lngIndex = 1
    Do While Not IsEmpty(Range("DEL_UNQ_TO").Offset(lngIndex, 0))
      .AddItem (Range("DEL_UNQ_TO").Offset(lngIndex, 0).Value)
      lngIndex = lngIndex + 1
    Loop
  End With
  
  With Me.ListBox2
    .Clear
    lngIndex = 1
    Do While Not IsEmpty(Range("DEL_UNQ9").Offset(lngIndex, 0))
      .AddItem (Range("DEL_UNQ9").Offset(lngIndex, 0).Value)
      lngIndex = lngIndex + 1
    Loop
  End With
End Sub

Ciao,
Holger

Hello, HaHoBe

i don't want to delete entire row, because there is data in other columns

i used ClearContents in my previous code

Gi, haroon2015,

nobody is deleting entire rows unless you changed the code I supplied - when using Clearcontents maybe clear the contents of the second entry on the list and report back about what happens to the length of your list when you use that command...

Ciao,
Holger