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]

The question is, when do you want this to run?
You could call the macro/VBA code to run manually, or you could have it run automatically on some event happening (like when cell AJ6 is manually updated)?

 

[Joe4]

If you want it to happen upon the entry of something in column AJ, this code should do that.
Just right-click on the sheet tab name, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

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

 

[MrRosco]

If you want it to happen upon the entry of something in column AJ, this code should do that.
Just right-click on the sheet tab name, select "View Code", and paste this code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

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

Hi Joe,

Thanks for getting back to me. i have tried this in my workbook and cell AI7 does not get deleted.

could this be due to the cell AJ has a formula?

Thanks
Ross

Thanks
Ross

 

[Joe4]

Yes, that will only work if the values in column AJ are being manually updated.
So, that takes me back to the question I asked in my first reply:

The question is, when do you want this to run?
You could call the macro/VBA code to run manually, or you could have it run automatically on some event happening (like when cell AJ6 is manually updated)?

 

[MrRosco]

Hi,

Apologies for that, cell AJ has a formula as its calculating actual sales and promotional cost. Cell AI is also calculated forecast.

is this something you can help with?

Ross

 

[Joe4]

is this something you can help with?

Yes, but I need you to answer my question.

I am not asking what is in each cell, rather I am asking when and how you want this to run?
Is there some "action" that you want to automatically trigger this process?
Or, do you just want a macro that you can call and run yourself whenever you want (if so, what range should it run on? all of column AI, or just certain rows)?

 

[MrRosco]

I would like it to be automatic, the trigger point will be when Cell AJ has a value (which if formulated) AI will be deleted.

the range will be all of AI & AJ,

anything else please let me know

Ross

 

[Joe4]

OK, so what is the actual formula in AJ?
I am guessing it is calling upon some other cells, and it is entry in to these other cells that is populating AJ. Is that correct?

 

[MrRosco]

Looking at it, i used an IF statement to reference another cell as i was thinking the LEN value might have something to do this.
however on reflection i think AI should be deleted if AO has an entry which is calculated. the formula is =SUM(AM7:AN7)

Hopefully this makes things easier.

Thanks again
Ross