Delete All Data Button

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

[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

[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

On Sat, 29 Apr 2006 13:14:01 -0700, Marcus <[email protected]>
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???

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

"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 <[email protected]>
> 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]

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


On Sun, 30 Apr 2006 11:08:01 -0700, Marcus <[email protected]>
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 <[email protected]>
>> 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???
>>
>>

[Marcus]

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

"Gord Dibben" wrote:

> 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
>
>
> On Sun, 30 Apr 2006 11:08:01 -0700, Marcus <[email protected]>
> 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 <[email protected]>
> >> 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]

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


On Sun, 30 Apr 2006 12:09:02 -0700, Marcus <[email protected]>
wrote:

>So do I need to create a button for this???
>
>"Gord Dibben" wrote:
>
>> 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
>>
>>
>> On Sun, 30 Apr 2006 11:08:01 -0700, Marcus <[email protected]>
>> 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 <[email protected]>
>> >> 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