[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

making cells zero/empty using vba in excel

Hi

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

=SUM(C7:C20)

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

Issac
0
IssacJones
Asked:
IssacJones
  • 2
  • 2
1 Solution
 
tdlewisCommented:
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

0
 
IssacJonesAuthor Commented:
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?
0
 
tdlewisCommented:
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.
0
 
IssacJonesAuthor Commented:
Many thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now