VBA to clear contents of a cell

[MrRosco]

Hi All,

I would like to know if it i possible to clear the contents of a cell once another has a value (or a LEN value greater than zero)

in my worksheet if cell AJ6 has a value then i would like AI6 to be deleted. i need this to happen for each row as at this moment i don't know how many entries i will have.

Thanks in advance for your help.

Ross

 

[Joe4]

So, are manual entries being made to the cells in columns AM and AN?
If so, then entries in to these cells would be the thing to trigger the VBA code to run.
Would you agree?

 

[MrRosco]

AM and AN both contain formulas, however AK is a manual entry which triggers the formulas in AM/AN. would this work?

 

[Joe4]

Yes, just change the code I posted to activate when a change is made to column AK, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

'   See if only one change, and change is to entry in column AK
    If Target.Count = 1 And Target.Column = 37 Then
'       If entry in column AJ is not blank, clear entry in column AI of same row
        If Target.Offset(0,-1) <> "" Then Target.Offset(0, -2).ClearContents
    End If
        
End Sub

Note how everything is relative to column AK (column 37).
AJ is one column to the left of AK, hence the Offset(0,-1)
AI is two columns to the left of AJ, hence Offset(0,-2)

 

[MrRosco]

Awesome, Thanks for all your help Joe!