Find and Replace all - Macro with user input

[ej463]

I would like to create a macros that asks the user for an "input" like with a prompt box. The Macros will search through the worksheet and replace the input by deleting whatever cells it is in.
I would like to do this twice, with two different inputs.

I was wondering if one could do this with partially complete inputs..
For instance I enter "red" in the prompt box, and it finds and deletes all cells with instances of "red dog" and "red" and "red tie".

Thank you so much for your help in advance.

[shg]

Use Ctrl+H to bring up the Find & Replace dialog.

[ej463]

yes i know but I want to add code to the beginning of a macros where a user can input text through two prompt boxes and it will find and delete all cells that contain that input. I was wondering if someone can tell me the code I should insert

[ej463]

Like I understand if I press record and do the find all and replace feature, I should be able to get the code for that. Thats great, but I do not know how to get the two prompt boxes to show up. So that the user after running the macros will be requested to type in two inputs that should be searched for in the spreadsheet and replaced. Thanks once again if you can help. I dont think its a difficult question.

[jaslake]

The following code will create the Input Boxes you require. You will need to write a Find routine to process these items and, yes, one can

do this with partially complete inputs

Please Login or Register  to view this content.

If you haven't figured this out yet, let me know.
J

[jaslake]

I had some time on my hands this evening as my house guests left earlier today. The attached workbook may be of some use to you. It will clear the contents of cells of all instances of the user prompts (with a wildcard). If the user enters "red" and "green" , it will clear "red", Red", "red bull", "red ball", "green", "Green", "green apple", etc.

Let me know how you make out.

J

PS: I found a bug in this worksheet; I've deleted the attachment. Will find a fix and repost.

[jaslake]

The attached workbook macros will prompt the user for two input values and will then delete (clear the contents) of all cells in the worksheet of those values. It uses a wildcard such that if the user enters "red" and "green" , it will clear "red", Red", "red bull", "red ball", "green", "Green", "green apple", etc.

Please note, the macro requires the entry of two values. It can be modified to accept only one value but your specs indicated two.

Let me know how you make out.
J

[Phil_V]

You may find this quicker as it uses Excels built in Find function, but I'm not sure how much faster it would be. It would depend on the size of your data set.

Please Login or Register  to view this content.

I have coded slightly differently to Jaslake, in that you can choose to have as many inputs as you like, and must use the [Cancel] button on the input box, (as directed) to exit the macro.

[jaslake]

I like it Phil.