Delete cell values based off of specified cell values

[Clarkit]

If I have a row of data and a cell that contains a few specific values in cell I10 (such as 'yes' 'no' 'maybe'), am I able to set something up so data in other cells (in the same row) is removed IF 'maybe' is the value in I10? Cells J10 and O10

Thanks for taking a look!

[DBY]

Hello
Assuming you wish to look down column 'I' and clear columns 'J' and 'O' on each row where column 'I' is equal to 'Maybe', then try something like:

Please Login or Register  to view this content.

This loops down to row I20 but can be made variable.

DBY

[Clarkit]

This would have to be attached to a button or something right? It wouldn't do it automatically, as soon as the 'i' cell is changed to 'maybe'? Would what I'm describing be able to be done with conditional formatting?

Hello
Assuming you wish to look down column 'I' and clear columns 'J' and 'O' on each row where column 'I' is equal to 'Maybe', then try something like:

Please Login or Register  to view this content.

This loops down to row I20 but can be made variable.

DBY

[DBY]

It could be triggered from a worksheet value change event for a particular column such as 'I'. Conditional formatting could only hide the values not delete them.

The Worksheet Change Event code would be something like:

Please Login or Register  to view this content.

[Clarkit]

It could be triggered from a worksheet value change event for a particular column such as 'I'. Conditional formatting could only hide the values not delete them.

The Worksheet Change Event code would be something like:

Please Login or Register  to view this content.

Should/ would I have to do anything so it only targets one sheet/tab? Also, how would you add more than one value to trigger the clear event? Say the other value that triggers the clear even is "no".

I tried using it. Couldn't get it to work... I don't need to use the code from post #2 in conjunction with your second post do I?
PS. I changed my event clear values to "future" and "NIC"
The cell which receives either of these values will already have a value in it if that changes anything.

This is what I tried

Please Login or Register  to view this content.

[DBY]

It will only work within the Sheet the Event is placed in, so if you've selected Sheet 1 for the value change event then that's the sheet in which it's triggered. It's triggered when the any of the designated cell's values are changed, so if they have values in there it shouldn't make any difference. The following code should work with either: "NIC" or "Future".

Please Login or Register  to view this content.

[Clarkit]

It will only work within the Sheet the Event is placed in, so if you've selected Sheet 1 for the value change event then that's the sheet in which it's triggered. It's triggered when the any of the designated cell's values are changed, so if they have values in there it shouldn't make any difference. The following code should work with either: "NIC" or "Future".

Please Login or Register  to view this content.

That works, thanks for your help!
Side note, I noticed you're unable to 'undo' 'control+z'
Is that just the nature of the beast?

[DBY]

Yes, unfortunately with VBA, Undo is not available to fall back on, so it's good to have regular backups of your file. You can put a check in before deletion but once it's gone it's gone. Glad to have helped.

Thanks for the Rep mark.

DBY

[Clarkit]

Yes, unfortunately with VBA, Undo is not available to fall back on, so it's good to have regular backups of your file. You can put a check in before deletion but once it's gone it's gone. Glad to have helped.

Thanks for the Rep mark.

DBY

A few follow up questions now that I have been able to test it out for a few days.
If I mark an item 'NIC' (by typing) it works great
If I mark an item 'NIC' (by typing) and then highlight that cell to drag down the contents of that cell (lower right hand corner of cell) to the next line (to avoid typing NIC multiple times) it won't delete the cells if you copy it down to more than 1 row below the item.(why?)
Also if you 'ctrl+c and ctrl+v' 'NIC' to another cell, it also doesn't delete the specified cells.(why?)

Separate 'issue'- not really part of the original question, but if I wanted to have ANY cell that contains 'Future' or 'NIC' already to have the specified cells deleted (basically no worksheet change event). What would you do?
Data gets copied from one worksheet to another (so many lines may already have the keywords that trigger cells to delete)

Thanks!

[DBY]

Hi
I can't stay at the PC long at the moment but copy and paste will not trigger the Change Event unfortunately. If you wish to copy across and then delete those cells, you could do that with a simple loop code. I'll post an example tomorrow if that would help.

DBY

[DBY]

Separate 'issue'- not really part of the original question, but if I wanted to have ANY cell that contains 'Future' or 'NIC' already to have the specified cells deleted (basically no worksheet change event). What would you do?
Data gets copied from one worksheet to another (so many lines may already have the keywords that trigger cells to delete)

Here is an example file of a loop. It loops through I10:I3500 looking for 'NIC' or 'Future' (not case specific). For each row it finds either value it clears contents of adjacent columns 'J' and 'O'.

Perhaps this would be a better solution for you.

DBY

[Clarkit]

Here is an example file of a loop. It loops through I10:I3500 looking for 'NIC' or 'Future' (not case specific). For each row it finds either value it clears contents of adjacent columns 'J' and 'O'.

Perhaps this would be a better solution for you.

DBY

Works well, Could a worksheet change even trigger this for me?

[DBY]

Yes it can be triggered by a Worksheet Change Event. You would need to decide what event to use.