Delete All Data Button
 

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???

Delete All Data Button
 

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


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=537503

Delete All Data Button
 

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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus
wrote:

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???

Delete All Data Button
 

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?

"Gord Dibben" wrote:

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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus
wrote:

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???

Delete All Data Button
 

Marcus

Check out F5SpecialConstants 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


On Sun, 30 Apr 2006 11:08:01 -0700, Marcus
wrote:

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?

"Gord Dibben" wrote:

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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus
wrote:

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???

Delete All Data Button
 

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

"Gord Dibben" wrote:

Marcus

Check out F5SpecialConstants 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


On Sun, 30 Apr 2006 11:08:01 -0700, Marcus
wrote:

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?

"Gord Dibben" wrote:

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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus
wrote:

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???

Delete All Data Button
 

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 ToolsCustomizeCommandsMacros. 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


On Sun, 30 Apr 2006 12:09:02 -0700, Marcus
wrote:

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

"Gord Dibben" wrote:

Marcus

Check out F5SpecialConstants 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


On Sun, 30 Apr 2006 11:08:01 -0700, Marcus
wrote:

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?

"Gord Dibben" wrote:

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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus
wrote:

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???





Gord Dibben MS Excel MVP