Clear specific cells using function & VBA? Haven't had success so far.

[Beta Test Team]

Hi forum, this is a great place! So far all of our questions have been answered here from searching your site without even having to register to post - impressive . But now we're stuck. It's likely so simple advanced Excel users don't think twice about it: how to use the VBA code one can find on the Interents from the worksheet?

Here's what we want to do, but please suggest other options if this is not smart. Excel 2013 here. Thus far the workbook as no VBA:

- Use a function (IF?) combined with VBA to clear specific user input cells when a specific cell lists text (Y/N), on the same worksheet. So for example, cell A1, A2, and A4 are all cleared when cell A10 prints the text "Y."

- Can this be done without VBA? (I'm pretty sure not.)

- Otherwise, we have found VBA for this purpose but all the solutions we find lack enough context to enable us to use them.

- Bonus points if there's a method to use a click-able button, so a user clicks the button and the cells are cleared.

 

[MARK858]

a method to use a click-able button, so a user clicks the button and the cells are cleared

Put the code below in a regular module, put a forms button on the sheet, right click the button, click assign macro, select the Clearme macro.

Sub Clearme()
Range("A1:A2,A4").ClearContents
End Sub

 

[Beta Test Team]

Put the code below in a regular module, put a forms button on the sheet, right click the button, click assign macro, select the Clearme macro.

Sub Clearme()
Range("A1:A2,A4").ClearContents
End Sub

Prefect, thank you very much.

 

[Michael M]

Shouldn't it be

Sub Clearme()
IF Range("A10").value = "Y" then Range("A1:A2,A4").ClearContents
End Sub

 

[MARK858]

My code was specifically in response to the quote below from the original question

- Bonus points if there's a method to use a click-able button, so a user clicks the button and the cells are cleared.

which I interpreted as being separate to the request for the cell have the "Y" entered in not as well as.
I could see no point having the button rather than just do it with event code when the "Y" was typed.

 

[Michael M]

Yep, fair enough....I read it the other way obviously......

 

[Beta Test Team]

My code was specifically in response to the quote below from the original question

- Bonus points if there's a method to use a click-able button, so a user clicks the button and the cells are cleared.

which I interpreted as being separate to the request for the cell have the "Y" entered in not as well as.

I could see no point having the button rather than just do it with event code when the "Y" was typed.

You're correct Mark, that is what I wanted. The reason I want a button is to offer a different way of clearing the cells. So with the button a user can click it at any point to start over (there are lots of input cells), and the VBA that Micheal posted will be used to automatically clear all the input cells if a specific result is found in a specific helper cell after entering all the input data.

Sorry if I wasn't clear. Thanks again!

 

[Beta Test Team]

Shouldn't it be

Sub Clearme()
IF Range("A10").value = "Y" then Range("A1:A2,A4").ClearContents
End Sub

Hello. Thanks for that, too

See what I wrote to Mark about use-cases. The VBA you posted will be very useful, and I can see how it can be edited to do other things, so it's a nice template for me to learn from.

Soon I'll be diving into the deep-ish end of VBA, so the more I can learn now the better. Cheers.