Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Ron Ron is offline
external usenet poster
 
Posts: 250
Default Resetting cells to zero value.

I have lots of cells with numbers. Can I place a button that will reset all
cells to zero?
Right now, when I want to start over, I go to each cell and change it to
zero. I have 250 cells.
Thank you if you can help with this.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Resetting cells to zero value.

Yes, you can definitely place a button that will reset all cells to zero. Here are the steps to do it:
  1. First, you need to add the Developer tab to the ribbon if it's not already there. To do this, right-click on any tab on the ribbon and select "Customize the Ribbon". In the Excel Options dialog box, check the box next to "Developer" and click "OK".
  2. Click on the Developer tab and select "Insert" from the Controls group.
  3. Scroll down to the "Form Controls" section and select the "Button" control.
  4. Click and drag to draw the button on your worksheet.
  5. Right-click on the button and select "Assign Macro".
  6. In the Assign Macro dialog box, click "New" to create a new macro.
  7. In the Visual Basic Editor, enter the following code:

    Formula:
    Sub ResetCellsToZero()
        
    Range("A1:Z250").Value 0
    End Sub 
    Note: Change "A1:Z250" to the range of cells you want to reset to zero.
  8. Click "Save" and close the Visual Basic Editor.
  9. Back in the Assign Macro dialog box, select the macro you just created and click "OK".
  10. Test the button by clicking on it. All the cells in the specified range should now be reset to zero.

That's it! You now have a button that will reset all cells to zero with just one click.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Resetting cells to zero value.

Select the 250 cells you want to reset to 0.
Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
Type this:
Selection.name = "ClearThemAll"
and hit enter

Now back to excel select any cell.
Start recording a macro.
Hit F5
type:
ClearThemAll
hit enter
type
0
hit ctrl-enter
(to fill all the selected cells with 0)
stop recording the macro (or select another cell first if you want).

You could modify the code to eliminate some of the .select's. And maybe even
add a prompt (are you sure?).

Option Explicit
Sub ResetTo0s()

Dim Resp As Long

Resp = MsgBox(prompt:="Are you sure you want to reset the values", _
Buttons:=vbYesNo)

If Resp = vbYes Then
ActiveSheet.Range("ClearThemAll").Value = 0
End If

End Sub

Then show the Forms toolbar. Drag a button from this toolbar (not the control
toolbox toolbar) to the worksheet. (Change the caption!)

And assign this macro to the button.



Ron wrote:

I have lots of cells with numbers. Can I place a button that will reset all
cells to zero?
Right now, when I want to start over, I go to each cell and change it to
zero. I have 250 cells.
Thank you if you can help with this.


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.newusers
Ron Ron is offline
external usenet poster
 
Posts: 250
Default Resetting cells to zero value.

Thank you! Just what the doctor ordered. Works perfect!
  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 48
Default Resetting cells to zero value.

Hi Dave,

I have this same problem only i want to "0" only a few cells here and there
on about 1500 rows. Is there a way of only zeroing certain cells easily.
Telling a macro to "0" only highlighted cells?

Struggling.....

"Dave Peterson" wrote:

Select the 250 cells you want to reset to 0.
Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
Type this:
Selection.name = "ClearThemAll"
and hit enter

Now back to excel select any cell.
Start recording a macro.
Hit F5
type:
ClearThemAll
hit enter
type
0
hit ctrl-enter
(to fill all the selected cells with 0)
stop recording the macro (or select another cell first if you want).

You could modify the code to eliminate some of the .select's. And maybe even
add a prompt (are you sure?).

Option Explicit
Sub ResetTo0s()

Dim Resp As Long

Resp = MsgBox(prompt:="Are you sure you want to reset the values", _
Buttons:=vbYesNo)

If Resp = vbYes Then
ActiveSheet.Range("ClearThemAll").Value = 0
End If

End Sub

Then show the Forms toolbar. Drag a button from this toolbar (not the control
toolbox toolbar) to the worksheet. (Change the caption!)

And assign this macro to the button.



Ron wrote:

I have lots of cells with numbers. Can I place a button that will reset all
cells to zero?
Right now, when I want to start over, I go to each cell and change it to
zero. I have 250 cells.
Thank you if you can help with this.


--

Dave Peterson



  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default Resetting cells to zero value.

You can select your cells individually (select the first and ctrl-click on
subsequent). Then give it a nice name.

If the cells are laid out nicely, you may be able to create a loop to do the
naming of the range.

Sammy wrote:

Hi Dave,

I have this same problem only i want to "0" only a few cells here and there
on about 1500 rows. Is there a way of only zeroing certain cells easily.
Telling a macro to "0" only highlighted cells?

Struggling.....

"Dave Peterson" wrote:

Select the 250 cells you want to reset to 0.
Hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
Type this:
Selection.name = "ClearThemAll"
and hit enter

Now back to excel select any cell.
Start recording a macro.
Hit F5
type:
ClearThemAll
hit enter
type
0
hit ctrl-enter
(to fill all the selected cells with 0)
stop recording the macro (or select another cell first if you want).

You could modify the code to eliminate some of the .select's. And maybe even
add a prompt (are you sure?).

Option Explicit
Sub ResetTo0s()

Dim Resp As Long

Resp = MsgBox(prompt:="Are you sure you want to reset the values", _
Buttons:=vbYesNo)

If Resp = vbYes Then
ActiveSheet.Range("ClearThemAll").Value = 0
End If

End Sub

Then show the Forms toolbar. Drag a button from this toolbar (not the control
toolbox toolbar) to the worksheet. (Change the caption!)

And assign this macro to the button.



Ron wrote:

I have lots of cells with numbers. Can I place a button that will reset all
cells to zero?
Right now, when I want to start over, I go to each cell and change it to
zero. I have 250 cells.
Thank you if you can help with this.


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Resetting multiple cells containing drop-down lists christophercbrewster via OfficeKB.com Excel Discussion (Misc queries) 3 August 13th 09 02:58 PM
Resetting cells Chuck Excel Worksheet Functions 1 December 17th 07 07:45 PM
password resetting Mohan Kumar Excel Discussion (Misc queries) 1 September 3rd 06 03:38 AM
Resetting values based on change in cells of other column NSteinner Excel Discussion (Misc queries) 1 September 4th 05 04:09 PM
Resetting the end of a worksheet Cachod1 New Users to Excel 1 March 29th 05 07:44 PM


All times are GMT +1. The time now is 12:40 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"