Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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??? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Data Button | Excel Discussion (Misc queries) | |||
How can I delete a macro when the Delete button is not active? | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Macro to delete data in 'green' cells only | Excel Worksheet Functions | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |