General discussion

Locked

Excel Macro help

By dschwer ·
Hi Im a macro newbie and was wondering if someone could help me design a macro that would take a range (column) of cells and reset those cells to zero (0). I have a spreadsheet that i input numbers into cells in multiple colums and each day and I zero the cells out manually. Is there any way to do this without recording a macro.

Thanks


Dale

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Macro help

by chitosunday In reply to Excel Macro help

Definitely, you need a macro to automate what you do regularly. Do the following steps:
1. Click tools, macro, record new macro
2. Then do what you ordinary do to zero the cells in excel like highlighting the cells and using the delete keys(All your steps will automatically be recorded and corresponding macro will be generated).
3. After you finished, go to tools , macro, stop recording macro or click the stop macro button.
4. Go to tools again, macro and options (below) and then put letter "a or any letter" in the box besides the shortcut key ctrl+ , click okey
5. Then press ctrl+a button (continuous without depressing ctrl button while pressing button "a".
You will see that your cells are clean.
In the tools macro visual basic editor, you must see the following:
sub macro1()
range("yourrange").select
selection.clearcontents
end sub

Collapse -

by dschwer In reply to Excel Macro help

Poster rated this answer.

Collapse -

Excel Macro help

by FirstPeter In reply to Excel Macro help

There are a couple of ways to do it without recording the macro. If you know the range (e.g., it's always rows 2 through 50):

Sub ZeroCells_KnownRange()
' First, open some variables for our starting and ending rows and our counter
Dim intStart, intEnd As Integer
Dim i As Long

' Next, we'll set our starting and ending rows.
intStart = 2
intEnd = 50

' Finally, we'll go ahead and run our loop, which will move from our start to end row and
' set the values to zero. This assumes the column is "A".
For i = intStart To intEnd
Range("A" & i).Value = 0
Next i
End Sub


If you only know where it starts:

Sub ZeroCells_UnknownRange()
' First, open some variables for our starting and row and our counter
Dim intStart AsInteger
Dim i As Long

' Next, we'll set our starting row.
intStart = 2

' Finally, we'll go ahead and run our loop, which will move from our start until it finds an empty cell and
' set the values to zero. This assumes the column is "A".
Do While Range("A" & i).Value <> ""
Range("A" & i).Value = 0
Loop
End Sub


There are other ways to do it, such as if you only want certain cells zeroed out (only cells that contain a "1", for instance), or if you have no idea what the starting/ending rows are. If you need one of those, let me know and I'll be more than happy to help!

Incidentally, using the "Record Macro" option is a great way to learn how to do stuff.

Good luck!

Collapse -

Excel Macro help

by kees.valkenswaard In reply to Excel Macro help

It depends on the shape of your columns whether you really need a macro.
Suppose the columns are next to each other.
Just drag over the area or use the select current region [or area] option. This is edit, go to.., special?, current region.
As you would want to do this every day add a button to one of your toolbars.
Click with the right mouse button in a free area of one of the toolbars. Customize?, click the middle tab, category edit, the bottom option is current region, a button with fourarrows. Drag to a bar until you see a capital I, release and close the customize window.
Make sure that there are empty rows and columns around your area. Click a cell, click the current region button, type 0 and use CTRL ENTER
That is all. If youmeant to empty the cells, just press DELETE after having selected the cells.
If your cells are in several areas. Just select area by area with dragging and using CTRL for selecting non-contiguous cells. After that again 0 and CTRL ENTER.
In my view this is safer than a just recorded macro which normally has a hard coded region in it.
Good luck.

Collapse -

by dschwer In reply to Excel Macro help

Poster rated this answer.

Collapse -

Excel Macro help

by Lord Foul In reply to Excel Macro help

when you are in recording mode, record this.

select the cells that you normally clear.
press delete on the keyboard.
click stop recording on the stop recording toolbar

this macro will delete the contents of your cells.

ghost

Collapse -

by dschwer In reply to Excel Macro help

Poster rated this answer.

Collapse -

by dschwer In reply to Excel Macro help

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums