Clear contents of 2 cells

[laguna92651]

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

[TMS]

You might want to try just changing .Delete to .ClearContents


Regards, TMS

[laguna92651]

Thanks, that clears the contents for the whole role I only want to clear the contents for the entries in columns AF and AG.
Al

[TMS]

    Range(Cells(findvalue.Row, "AF"), Cells(findvalue.Row, "AG")).ClearContents

[laguna92651]

Thanks so much, worked great. If I wanted to clear a wider range of cells and a non-adjacent cell, would this be the correct format?

Range(Cells(findvalue.Row, "AF"): Cells(findvalue.Row, "AJ"),Cells(findvalue.Row, "AO")).ClearContents

Thanks again,

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[laguna92651]

Thanks for the help, much appreciated.

[TMS]

No, you can't do that. Range expect a start cell and an end cell. There are different ways of providing that; in this case, two Cells() separated by a comma.

You could use Union to build up a combined range, for example:

?Union(Cells(1, "AF"), Range(Cells(1, "AJ"),Cells(1, "AO"))).address
$AF$1,$AJ$1:$AO$1

Regards, TMS

[TMS]

You're welcome. Thanks for the rep.