Remove records from listbox

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hi guys,

I am using the next code to transfer records from a listbox1 to specifics sheets. Is there anyway to delete those records onece they have been transfered. Thanks in advance

Private Sub CommandButton1_Click()
Dim sTgtSht As String
Dim rngLastCell As Range
Dim lLastRow As Long
Dim sActiveSheet As String

sActiveSheet = ActiveSheet.Name

sTgtSht = ComboBox1.Value

If (sTgtSht = "") Then
MsgBox "No target sheet select."
GoTo End_Sub
End If

On Error Resume Next
Sheets(sTgtSht).Select
Err.Clear
On Error GoTo 0

If ActiveSheet.Name <> Sheets(sTgtSht).Name Then
MsgBox "Sheet '" & sTgtSht & "' not found."
GoTo End_Sub
End If

Set rngLastCell = Sheets(sTgtSht).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)

If rngLastCell Is Nothing Then
lLastRow = 1
Else
lLastRow = rngLastCell.Row
End If

Set rngLastCell = Nothing

For iListCount = 0 To ListBox1.ListCount - 1

If ListBox1.Selected(iListCount) = True Then

lLastRow = lLastRow + 1
ListBox1.Selected(iListCount) = False
Sheets(sTgtSht).Cells(lLastRow, "A") = ListBox1.List(iListCount, 0)
Sheets(sTgtSht).Cells(lLastRow, "B") = ListBox1.List(iListCount, 1)

End If

Next iListCount

End_Sub:
Sheets(sActiveSheet).Select

End Sub

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Man you dont have to repeat same question again and again. If you just want to keep it at the top, just add a new comment and it will go at the top.