Clearing/reset cells with a button

Ludo

New Member
Joined
Dec 8, 2005
Messages
6
After put in lots af data, it would be easy to erase the sheet it in one go, in stead of clearing 'cell by cell".a kind of 'reset' button... and i'm talking about just the data cells, obvious not the formula cells.

I'm quite a newb on programming buttons, is there any one who can help me out with some simple lines.

tnx,
Ludo
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Ludo !!
Welcome to the Board !!!!!!!!!!!!!!

I know who you are :) :)

control-click your cells you want to reset
click in the namebox
type a name like "erase_range"

code
Code:
Sub test()
Range("erase_range").ClearContents
End Sub

glad to be there whan you posted for the first time !!!

ENJOY the Board !

kind regards,
Erik
 

NathanPage

New Member
Joined
Dec 8, 2005
Messages
9
The simplest way I know how to do this is to record the macro you need.

Create the cell, with data in each of the cells that you will want cleared.
Click on Tools -> Macro -> Record New Macro.
Type in a name for the macro, and a shortcut key if you wish
Select each cell in turn that you will want to clear, and press the delete key.

When you've cleared all the cells, stop the macro recording (either Click Tools -> Macro -> Stop Recording, or click the "stop" button that should have appeared on your screen when you started recording the macro)

Now, whenever you hit the shortcut key combination, it will clear those cells. You can also link the macro to a macro button directly.
 

Ludo

New Member
Joined
Dec 8, 2005
Messages
6
Thnx guys, it seems to work perfect.

Second problem now is, when i protect the sheet after i made the button, and i wanna test it again, i get an error saying the cells are not changeble ??? Allthou they are unlocked ?

What did i missed ?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832

ADVERTISEMENT

Hello, Ludo,

be sure you missed to unlock at least one cell !

try this on a copy of your spreadsheet
Code:
Sub test()
For Each cell In Range("erase_range")
If cell.Locked Then cell.Interior.ColorIndex = 6
Next cell
End Sub
locked cells will turn yellow

best regards,
Erik
 

ptreanor

New Member
Joined
Sep 30, 2005
Messages
6
An easier way might be to clear just the constants, (ie any cell without a formula). If you need some values to remain just turn them into a formula, eg ="Heading"

The following should clear all non-formula cells:

Sub test()
Cells.Select
Selection.SpecialCells(xlCellTypeConstants).ClearContents
End Sub
 

Ludo

New Member
Joined
Dec 8, 2005
Messages
6
Yeps, it runs perfect now, there where a couple cells who where still locked.

Ok since i'm a lazy git, i'll get on with questions: can you validate a button too; like "are you sure you wanna take this action?" when you press the button?
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Ok since i'm a lazy git, i'll get on with questions
this will not be invite much people to help: mainly we try to help people which try out things themselves but get stuck on some details
for this time, I'll give you a little present
Code:
Sub test()
If MsgBox("sure?",292,"title") = vbNo Then Exit Sub
Range("erase_range").ClearContents 
End Sub
if any problems feel free to reply (don't call me)
 

Watch MrExcel Video

Forum statistics

Threads
1,119,127
Messages
5,576,235
Members
412,709
Latest member
AD04
Top