If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
#3
|
|||
|
|||
Resetting cells to zero value.
Thank you! Just what the doctor ordered. Works perfect!
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|