Find and clear cell values that equal the active cell value in range

  • I have a userform that will insert a value into the active cell on an event click. What I would like to do is have excel search a specific range and clear any cell that contains that value. I've looked at various examples, and being that i'm a VBA novice, i'm not sure where to start. The range is rather funky as well. The range it would need to search is for example ($X$8:$AS$50,$X$53:$AS$58). Thanks in advance for any help!!

  • Re: Find and clear cell values that equal the active cell value in range



    Something like...?


    Written freehand and 100% guaranteed untested - meant as an example only, not finished, working, debugged code.

  • Re: Find and clear cell values that equal the active cell value in range


    I tested it and it ends up clearing every value in the range instead of any value that equals the active cell. I put the code above into a module, and called it from the userform click after it inserts the value into that current cell.

  • Re: Find and clear cell values that equal the active cell value in range


    Something like this? I tested it according to your explanation. You will insert a name into a cell via the Userform, then run the code on the Selected cell and remove any matching names.


    [SIZE=3]The Only Dumb Question Is A Question Not Asked.[/SIZE]

  • Re: Find and clear cell values that equal the active cell value in range


    Still not working for me. It just clears the current cell. If I place it before the code to insert the value from the userform it works after clicking it twice.

  • Re: Find and clear cell values that equal the active cell value in range


    Never seen anything quite like that...


    However, it would have been helpful if you had mentioned they were merged cells in the first place.


    Also, I assumed you would have realised that you had to pass a value to the procedure for it to use for the comparison. The declared but (seemingly) never initialiased variable 'vvalue' is Empty and that matches the empty cells in the merged area which then clears the merged area.


    Make sure that you initialise vvalue... but I would consider another way to do it. Merged cells are more trouble than what they're worth, as you are finding out.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!