Use VBA to select a cell in a column, and then delete it.

[smith914]

I currently have the code below;

Private Sub CommandButton1_Click()
Dim Response As VbMsgBoxResult
 Response = MsgBox("Are you sure you want to delete this waiting list?", vbExclamation + vbYesNo)
If Response = vbNo Then Exit Sub
 Sheets("Home").Select
 Sheets(ComboBox1.Value).Delete
 Unload Me
 MsgBox "The worksheet has been deleted", vbInformation
End Sub

Which selects a sheet based on a combo box and then deletes it.

The data that goes into ComboBox1 is based on the names of the spreadsheet, so what I would also like to be able to do is to delete the data in the cell that contains the spreadsheet name.

I don't know if it would be easier to delete the data in the cell and then re-sort the list to remove the gap, that would appear, or if I should delete the whole row?

The next bit of code is what I use to auto sort the list;

Columns("N:N").SelectSelection.Sort Key1:=Range("N1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal