I am using the following code, and it works fine. It does what it is suppose to, if finds the row that has the value in the form control Act1 and deletes that whole row in the worksheet. Unfortunately, when the row gets deleted other data in other ranges on the worksheet also get deleted, I trying to avoid moving the list I want to delete from to a new sheet. What I would like to do instead of deleting the row is to find the row and clear the contents of the two columns, AF and AG, in the range and then sort the list, this will give me the effect I want, this range is for a dropdown list that I should have put somewhere else to begin with.
Private Sub cmdDelete_Click()
'declare the variables
Dim findvalue As Range
'check for values
If Act1.Value = "" Then
MsgBox "There is no data to delete"
Exit Sub
End If
'looks for Code and delete the row
Set findvalue = Sheet3.Range("AF:AF").Find(What:=Act1, LookIn:=xlValues)
findvalue.EntireRow.Delete
'clear the controls
For XP = 1 To 3
Me.Controls("Act" & XP).Value = ""
Next
SortActivity
End Sub
Bookmarks