making cells zero/empty using vba in excel

Posted on 2012-09-04
Last Modified: 2012-09-05

Can anybody help with the following?

I have an Excel spreadsheet in which I have a few columns that are summed with a total underneath. For example, a cell that contains a sum uses the following formula


I have added a button to the Worksheet and I would like to use VBA to re-set the values that are being summed to be zero (or better still empty) once the button is pressed.

I suppose I could do this manually by specifying C7 to C20 in the code but, thinking ahead, I would like to be able to deal with the possibility that another row is added in this range. If this were done I would have to change the VBA code.

Can anybody suggest I way I could do this. I would also appreciate some code that would deal with setting the values to zero i.e. I'm new to VBA.

Another thing I want to do is to copy values from one column to another. Again, I would like the code to be able to deal with the possibilities that new rows may be added to the range i.e. I don't have to manually change the VBA code.

Is there a way to do this?

Thanks in advance

Question by:IssacJones
    LVL 10

    Accepted Solution

    If you want the range to be easily changed, you should name it. For example, select C7:C20 and use the Name Manager to call it "ToBeSummed". Change the formula to "=SUM(ToBeSummed)".  This subroutine can then be used reset the values to zero:
    Sub ResetValues()
        ActiveSheet.Range("ToBeSummed").Value = 0
    End Sub

    Open in new window


    Author Comment

    Thanks tdleis. That looks promising.

    By the way, do I need to change the formula to "=SUM(ToBeSummed)"? Wouldn't this still work even if I don't do this last step?
    LVL 10

    Expert Comment

    It would still work, but it will probably be easier to understand everything that's going on in the spreadsheet if you use the name consistently.

    By the way, if you add rows in the middle of the range, formulas and the named range will adjust automatically. It's when you want to add to the end of the range that you have to make manual adjustments.

    Author Closing Comment

    Many thanks

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    How to add prompts to user defined function? 6 27
    Vlookup for IP 3 37
    Excel File Date Format 6 18
    Import csv files to MS SQL 5 38
    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now