Delete All Data Button

M

Marcus

Hello,
I am trying to create a command button that will delete all the data in
the workbook. I have never really created any command buttons though. Can
anyone help me with this???
 
M

mrice

You need to add a button from the forms toolbar and link to the
following macro.

Sub DeleteAll
For each sheet in sheets
Cells.clear
Next Sheet
End Sub
 
G

Gord Dibben

Wouldn't it be easier just to delete the workbook/file from your HDD?

Or did you mean something else when you wrote "delete all the data in a
workbook"?

Delete data but not formulas or some other parts?


Gord Dibben MS Excel MVP
 
M

Marcus

Basically what I need this for is I have a spreadsheet for 10 stores that I
am over. Each store has their own sheet. Example (6600, 6601 & so forth).
These sheets are used to collect data on a monthly basis. However I ownly
need the data for that month. So at the end of the month I need to delete the
data for that month. The problem is that I have a bunch of formulas that I
don't want to re-write each month. So I want a button that will delete all
the data in cells that are UNprotected. Any ideas?
 
G

Gord Dibben

Marcus

Check out F5>Special>Constants for selecting the type of data you wish to delete
and that which you wish to maintain.

You can record a macro while doing this.

Alternative.............

Run this macro to select all non-protected cells then delete them.

Sub UnLocked_Cells()
'Bob Flanagan
Dim cell As Range, tempR As Range, rangeToCheck As Range
'rotate through all cells in the selection
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not cell.Locked Then
'add to tempR if unprotected
If tempR Is Nothing Then
'do it this way for the first unprotected cell
Set tempR = cell
Else
'add subsequent cells this way.
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'do this upon completion of the For..Next checking
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If

'select qualifying cells
tempR.Select
Selection.ClearContents
End Sub


Gord Dibben MS Excel MVP
 
M

Marcus

So do I need to create a button for this???

Gord Dibben said:
Marcus

Check out F5>Special>Constants for selecting the type of data you wish to delete
and that which you wish to maintain.

You can record a macro while doing this.

Alternative.............

Run this macro to select all non-protected cells then delete them.

Sub UnLocked_Cells()
'Bob Flanagan
Dim cell As Range, tempR As Range, rangeToCheck As Range
'rotate through all cells in the selection
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not cell.Locked Then
'add to tempR if unprotected
If tempR Is Nothing Then
'do it this way for the first unprotected cell
Set tempR = cell
Else
'add subsequent cells this way.
Set tempR = Union(tempR, cell)
End If
End If
Next cell
'do this upon completion of the For..Next checking
If tempR Is Nothing Then
MsgBox "There are no UnLocked cells in " & _
"the selected range."
End
End If

'select qualifying cells
tempR.Select
Selection.ClearContents
End Sub


Gord Dibben MS Excel MVP
Click to expand...
 
G

Gord Dibben

You can create a button and assign the macro to it.

Open the Forms Toolbar and click on the button icon then draw one on your sheet.

Or Tools>Customize>Commands>Macros. Drag the smiley-face up to any Toolbar and
assign a macro to that by right-clicking and choosing from the options..

May be a good idea if you visited David McRitchie's site for more on
"getting started" with macros.

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Gord


So do I need to create a button for this???
Click to expand...

Gord Dibben MS Excel MVP
 
Top