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!!
Find and clear cell values that equal the active cell value in range
-
-
-
Re: Find and clear cell values that equal the active cell value in range
Code
Display MoreSub ClearRange(vValue as Variant) Dim c as Excel.Range For Each c in Range("$X$8:$AS$50,$X$53:$AS$58") If c.Value = vValue Then c.ClearContents End If Next End Sub
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
OK, I've tested it and it only cleared matching values...
Post a sample copy of your workbook
-
Re: Find and clear cell values that equal the active cell value in range
I think its not working because it has something to do with the fact that the values are in merged cells. So when I changed the code to include "mergeArea", it deletes all the values.
-
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.
-
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!