Delete Formula

[ibrahimaa]

In Excel file, the user will enter 1 in A1, 2 in A2 then if he entered 3 in A3, I want the 1 in A1 (user already entered) to disperse automatically. If Macro is the only way to do this, can I use “record macro” to record specific steps and then run it for example in a command button? I would appreciate any help. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

 

[Sahak]

Hi ibrahimaa

Try this:
Copy this code
Right click on sheet tab
Select “View Code”
Paste

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("A1") = 1 And Range("A2") = 2 And Range("A3") = 3 Then Range("A1") = ""
End Sub

If user will enter 1 in A1, 2 in A2 then 3 in A3, A1 will be cleared automatically.

 

[SteveO59L]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 3 And Target.Column = 1 Then

If Target.Offset(-1, 0) = 2 And Target.Offset(-2, 0) = 1 Then
Target.Offset(-2, 0).ClearContents
End If
End If
End Sub

 

[Greg Truby]

• Please clarify - were you specifying desired values? Or if they contain any value?
• Just these cells? Or any time three cells that are vertically contiguous and all three are non-blank, clear the first?
• @ Steve any time you change a value inside a _Change event handler, you need to toggle EnableEvents lest you inadvertantly create an infinite loop. In this case the values testing will prevent this - but there's always a danger that later on the code gets edited and suddenly what once worked starts locking up Excel due to infinite looping.