vba - In Excel, how can I clear the contents of a cell referenced in another cell?

23
2014-04
  • Nick K

    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?

  • Answers
  • Sean Cheshire

    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.

  • Manoj Agarwal

    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.


  • Related Question

    Basics of how to make a VBA procedure run in excel when a Cell is changed
  • Vaccano

    I am trying show some data from my SQL Server in excel. Because I have a query that hits 3 tables and uses a parameter, I am going to have to use VBA.

    I am not sure how to get the VBA to "auto run". Doing a normal query in Excel gives you options like running when the sheet is open or auto refreshing. Since I am adding a parameter, I would like the my Sub to run when I change the value of the cell the parameter is in.

    Any ideas?


  • Related Answers
  • dkusleika

    Right click on the sheet tab and choose View Code. Put code like this in the sheet's module

    Private Sub Worksheet_Change(ByVal Target As Range)
    
        If Target.Address = "$A$1" Then 'only run if the correct cell is changed
            MySub 'this is the name of your vba procedure
        End If
    
    End Sub
    

    This sub will run whenever a cell on that sheet is changed. If the cell that's changed is A1 (change to suit your data), then a procedure in a standard module named MySub (change to suit) will be run.

    I'm not sure why you have to use VBA though. It seems like you could write SQL to pull from as many tables as you want and include a parameter.

  • Sux2Lose

    Put:

    application.volatile
    

    in your code.

  • wbeard52

    For sake of clarity I am going to say that the parameter is in CELL A1 and the result of the VBA code is listed in CELL A2.

    In VBA module

    Function Test1(strText as string) as string
       Test1 = strText & "15"
    End Function
    

    In Cell A2 place the equation "=Test1(A1)
    In Cell A1 place the parameter you want to change

    I would verify it works and then place your other VBA code in to have the code run whenever A1 changes.

  • Michael

    dkusleika's answer looks best; things get a little trickier if you want to to do the same for a range. The following code will occur whenever a cell within the specified area is modified.

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim woArea As Range, isect As Range
    Set woArea = Sheet1.Range("A1:A500")
    Set isect = Application.Intersect(Target, woArea)
    
    If isect Is Nothing Then
        ' Nothing happens
    Else
        ' Stuff you want to happen goes here
    End If
    
    End Sub