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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,621
Messages
5,832,736
Members
430,160
Latest member
a_majda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top