I have a Userform listBox that displays data from the worksheet. I want to delete the worksheet row based on the selection on listBox.
The code deletes the row above, not the selected row according to listbox.
Private Sub CommandButton3_Click()
Dim i As Integer
For i = 0 To Range("A65356").End(xlUp).Row - 1
If lstDisplay.Selected(i) Then
Rows(i).Select
Selection.Delete
End If
Next i
End Sub
Try this on some test data...
Sub CommandButton3_Click()
' Will move data to array and then move wanted data
' to a second array and repaste that back on to the spreadsheet
' currently TestCondition is the comparison in column A
' Change Sheet1 if your worksheet name is different.
Const COMPARE_COL As Long = 1
Dim a, aNew(), nr As Long, nc As Long
Dim r As Long, c As Long, rNew As Long
Dim TestCondition As String, tmp
a = Worksheets("Sheet1").UsedRange
nr = UBound(a, 1)
nc = UBound(a, 2)
ReDim aNew(1 To nr, 1 To nc)
rNew = 0
TestCondition = lstDisplay.Selected(a)
For r = 1 To nr
tmp = a(r, COMPARE_COL)
If tmp <> TestCondition Then
rNew = rNew + 1
For c = 1 To nc
aNew(rNew, c) = a(r, c)
Next c
TestCondition = tmp
End If
Next r
Worksheets("Sheet1").UsedRange = aNew
End Sub
When deleting items from a list, always work from the end to the beginning, never from beginning to the end. In top of that, there's no need to select when you want to do something, so I would advise the following (not tested):
For i = Range("A65356").End(xlUp).Row - 1 DownTo 1
If lstDisplay.Selected(i) Then
Rows(i).Delete
End If
Step -1
By the way: 65356 looks so familiar and similar to 65536 (2^16). I suspect there's a typo here :-)
i + 1....? Also, better not to delete in a loop. UseUnionto create a range to delete, and delete it after the loop. Also useLong, notInteger. – BigBen