Clear Contents of multiple cells based of one cell

Hello,

I have values in column A, B, and C. When the value in cell A is deleted, I would like to have the cells in B and C removed as well. The following code works great for removing cells only column B, but I get errors when trying to add code for column C.

Please Login or Register  to view this content.

This is what I tried, but my workbook freezes:

Please Login or Register  to view this content.

Any help on this would be much appreciated!

Try this:

Please Login or Register  to view this content.

Hi stnkynts,

This worked fantastic, thank you! I actually have a few follow up questions since I will apply this to a much more complex spreadsheet. (I apologize for not including this in the original post, but I didn't want it to scare off any responses.)

1. If the cell in column A is blank to begin with, I would like to make sure no content can be added to columns B and C. i.e. Cells B and C can only contain values when cell A does.
2. If I would like to add another column "D" which would act the same as columns B and C, how would I go about including this in the range?
3. If the value in column A contains a reference formula, how can the code recognize that the value = "" from the reference cell? Right now the current code only recognizes a completely empty cell.


Many thanks again for your help & quick response on the previous post.

3. If the value in column A contains a reference formula, how can the code recognize that the value = "" from the reference cell? Right now the current code only recognizes a completely empty cell.

Not enough information provided to accurately answer this question. Where is this reference cell in relation to that row column A? What is the formula? Do the formula actuall become "" or does it become 0, #N/A, etc.

This answers your other questions:

Please Login or Register  to view this content.

Hi stnkynts,

The code you've written has solved the first two issues, so thank you for that.

In regards to #3, the cell reference is coming from another worksheet called "Data" in the same workbook (FYI all the values in "Data" are referenced from another workbook). If this formula below is TRUE, it will always return a number. If it is FALSE, then it will return "".

Please Login or Register  to view this content.

Any ideas on this? Should I create a new post with the information already provided?

You can if you want. The reason I did not reply is because I am not experiencing what you are saying. On a test workbook everything seems to work fine for me. You could always submit an example workbook.

Here is a test file that will show you the issue.

When the values in the "Input Sheet" are removed, I would like the relevant content in Columns D-F in "Sheet1" to be erased. Right now they only erase when I press enter on the relevant cell in Column B to trigger the code.

Please let me know if you would like any clarification on this. Thanks again.

Let me make sure I understand your workflow. You make changes in the Input sheet. And when you make a change, such as deleting a value in column A of the Input sheet, you want to locate the now deleted value in column B of Sheet1 and clear columns D-F of the row. If that is the case, I don't think you could have made it any more difficult on yourself (me). try this: (Note: You need to put this code in the Input Sheet, not sheet1. And you need to remove the worksheet_change event procedure from sheet1 since it will conflict with input sheet code. I can fix this later but to be honest I don't really have the time at the moment)

Please Login or Register  to view this content.

I've been thinking of ways to simplify this issue to avoid complex code, and perhaps this idea might make sense. Let's assume this code is still in place:

Please Login or Register  to view this content.

If the value in cell A2 is removed in "Input Sheet", then the content in cells D2, E2, F2 in "Sheet1" will obviously still remain. However, what if a code was written on WorkbookOpen() to search for cells that contain text in D2:F21 in "Sheet1", and simply click & press enter on those cells so that the contents will delete itself when it realizes that its corresponding cell in column B = "".

I should point out that 99% of the time there will be only 1-5 rows that will contain text in columns D, E, F. It would be very uncommon for many fields to be filled at one time. Hopefully this would mean the code would not take much time to run.

stnkynts, does this seem like a viable solution to you? Or am I oversimplifying a more complex issue? Thanks for your input.