Forum Discussion
Excel VBA data entry form 2016
Dear forum:
Device: HP laptop computer 16g RAM 500 HD
OS: Windows 10 Professional latest edition
I am beginning a simple application for contacts in Excel 2016 using VBA
code and its User form.
Everything is working just fine save for the DELETE button code which
I am not sure why it is not working. It deletes the rows however,
it does not delete the correct corresponding row in the spreadsheet
(worksheet) itself.
Here is the code I am using:
Private Sub btnDelete_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
The intellisense does not work in ("Rows(1).Select and
neither does it work on the next line (Selection.Delete).
Any help to get my DELETE button to work will be greatly
appreciated. Again, it deletes the row I select in the listbox
(lstDisplay) but does not delete the same row in the worksheet,
Thank you much in advance to all.
Deee
When using a for loop to delete rows, you should work your way from the bottom up. Otherwise, deleting rows will throw off your loop counter. Also, since the listbox is zero based, I believe you will need to add 1 to delete the intended corresponding row. Be sure to backup your data before testing.
Private Sub btnDelete_Click() Dim i As Long For i = Range("A65356").End(xlUp).Row - 1 to 0 Step -1 If lstDisplay.Selected(i) Then Rows(i+1).Delete End If Next i End Sub
6 Replies
When using a for loop to delete rows, you should work your way from the bottom up. Otherwise, deleting rows will throw off your loop counter. Also, since the listbox is zero based, I believe you will need to add 1 to delete the intended corresponding row. Be sure to backup your data before testing.
Private Sub btnDelete_Click() Dim i As Long For i = Range("A65356").End(xlUp).Row - 1 to 0 Step -1 If lstDisplay.Selected(i) Then Rows(i+1).Delete End If Next i End Sub- Dear JMB17:
Thank you so much for explaining the reasons why this code should
be written like this. I have done similar in VB6 but the code is practically
entirely different.
I inserted your suggested CODE and it works perfectly.
Thank you so much
I am now working on the last button which I most probably will
need help with and that is the SEARCH button.
I would certainly appreciate your help with that however, if you can't,
I understand.
Thank you nonetheless,
Dwight- Sure, go ahead.
