Delete row from ListBox with RowSource enabled

[christobal]

Have A ListBox which is populated by the RowSource property
When trying to delete a selected row the following code only delete
the first row and not the selected row.

Private Sub cmddelrefresh_Click()
'Deletes items from randomize sheet shows in listbox
response = MsgBox(" delete " & ListBox1, vbOKCancel)
If response = vbCancel Then Exit Sub
'Sheets("Randomize").Activate
Range("Clubname").Select
For Each c In Range("Clubname")
If c.Value = ListBox1 Then
ActiveCell.EntireRow.Delete '(This always goes to top row of Range)
Exit For
End If
Next

End Sub

How is it possible to rectify this proble

 

[Bob Phillips]

Hi Christobal,

Change this line

ActiveCell.EntireRow.Delete

to

c.EntireRow.Delete

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Chip Pearson]

There are two things wrong with your code. First, when you use a
For Each loop, the ActiveCell never changes, so your code within
the 'For Each c In Range("ClubName")' is always refering to the
same cell. In a For Each loop, you'd want to write code like

For Each c In Range("ClubName").Cells
If c.Value = ListBox1.Value Then
' delete
End If
Next c

The second problem is that when you are deleting rows in a loop,
you *always* want to loop from the bottom up, not from the top
down. Otherwise, you'll possibly miss some rows. Try something
like

Dim RowNdx As Long
Dim LastRow As Long
Dim FirstRow as Long
FirstRow = Range("ClubName").Row
LastRow = FirstRow + Range("ClubName").Rows.Count -1
For RowNdx = LastRow To FirstRow Step -1
If Cells(RowNdx,Range("ClubName").Column).Value =
ListBox1.Value Then
Rows(RowNdx).Delete
End If
Next RowNdx


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com