How to delete the item user deletes from a drop down list?

[pelumi10]

Hi,
I created a dynamic drop down list for column 26 with this code :
=INDIRECT("Sheet1!$J$2:$J$" & COUNTA(Sheet1!$J:$J))

Then I linked the list the list with this code:
Dim SubProcess As String
Dim LastRow As Integer
Select Case Target.Column
Case 24
SubProcess = Cells(Target.Row, Target.Column)
If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("J:J"), SubProcess) = 0 Then
LastRow = Sheets("Sheet1").Cells(Rows.Count, 10).End(xlUp).Row + 1
Sheets("Sheet1").Cells(LastRow, 10) = SubProcess

So the logic is, column 24 is empty, when a user enters an item in column 24, it shows up as a drop down list in column 26
But the problem is if a user clears an item from the column it still shows on the list. How do I get the item the user deletes from showing up in the list?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Delete the item user deletes from a drop down list
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.