Search code examples
excelvbareplaceuserformvba7

Using replace function for this code instead of delete

I have created a user form for data entry and all entries shows in a list box , im still new to this so bare with me. when I select a line from the List and click delete, I want it to replace the values with "XXX' instead of delete row so that I keep the sequence of the serial number that I have, because the function that i have is that it counts nonblank cells so when i delete it adjusts the serial number according to nonblank cells.

How can I use replace function in this

ThisWorkbook.Sheets("NEWROUND").Rows(selected_List + 1).Delete

so that it can replace with "XXX" instead?

the code for the delete button is this:

Private Sub ButtonDelete_Click()


If selected_List = 0 Then

MsgBox "No Delegation is selected.", vbOKOnly + vbInformation, "Delete"
Exit Sub
End If

Dim i As VbMsgBoxResult
i = MsgBox("Do you want to delete the selected Delegation?", vbYesNo + vbQuestion, "Confirmation")
    
If i = vbNo Then Exit Sub


ThisWorkbook.Sheets("NEWROUND").Rows(selected_List + 1).Delete

Call RESET
    
MsgBox "Selected Delegation is Deleted.", vbOKOnly + vbInformation, "Deleted"
     

End Sub

please help!!

How can I use replace function in this

ThisWorkbook.Sheets("NEWROUND").Rows(selected_List + 1).Delete

so that it can replace with "XXX" instead?

Solution

  • The answer is by by Tim Williams in the comments but wanted to answer it so that the question is closed. because it was very helpful.

    worked like a charm !

     ThisWorkbook.Sheets("NEWROUND").Cells(selected_List + 1, _

    ("A").Resize(1, 10).Value = "XXX"

    (eg.) to fill first 10 cols with "XXX" That's not a good way to handle a serial number though. You could instead use a number stored in a cell on a hidden sheet, or in a defined Name in the workbook. For example see stackoverflow.com/a/12058811/478884 –