Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am trying to clear the contents of a cell based on an indirect reference. For example, cell A1 contains the address to another cell (i.e. B2). Any idea how to clear the contents of the cell referenced in A1? To be clear, I am not trying to clear A1, but rather I am trying to clear the contents of the cell referenced in A1.

Any thoughts on how to capture that in code?

share|improve this question
1  
At first I thought: did you try the delete button? On the second hand, you've added the VBA tag. Do you want it to happen through VBA? –  Jacob Jan Tuinstra Dec 11 '12 at 21:13
    
Hmm, the delete button would be nice, but I am trying to get it done through VBA. I tried to simplify the scenario as much as possible. In the end, I want the reference to the other cell (done via INDIRECT) to remain, but the referenced cell to be cleared. –  Nick K Dec 11 '12 at 21:32
    
Perhaps you can clearify your question a bit more, to use the words VBA and INDIRECT. Add some styling to it as well, so that those key words pop-up! Goodluck. You might want to read the faq section as well. –  Jacob Jan Tuinstra Dec 12 '12 at 6:44

2 Answers 2

You would have to resort to VBA for that functionality.

The code is simply:

Sub clearcell()
Range(Range("A1").Value).ClearContents
End Sub

which will look at the cell reference in A1, get the address, and clear everything from those cells. Note that this will also run on multiple cells, not just one. If you have B1:C10 in cell A1, the whole area of B1 to C10 will be cleared.

share|improve this answer

Copy A1, that contains the formula, paste as value in A1 and then you can delete the value from B2. In this case, as A1 contains a value and not the formula, deleting B2 value will have no impact.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.