Mutiple Clear Data Macro
May 16, 2009
I have made a spreadsheet but would love to be able to incorporate multiple 'Clear Data Buttons', (Macros)
that clear cell data.
I have attached several screen shots of my spreadsheet, with the URLs listed below.
(Images 1-6)
http://tinyurl.com/qba57l
http://tinyurl.com/od9upb
http://tinyurl.com/ovaeej
http://tinyurl.com/pctsvj
http://tinyurl.com/oc2qqm
http://tinyurl.com/qdhzge
Spreadsheet (xls & xlsm) in zip format
http://tinyurl.com/qczlap
It is a trading order sheet that I want to use, but also upload to a blog that I have just
started, relating to trading as a free download, hopefully it may benefit some users/visitors etc.
OK, regarding the macros.
I'm not quite sure how it is best to do it.
In total there are 13 'Clear Buttons.'
11 are 'CLEAR DATA' Buttons
12 is a 'Clear ONLY ORDER SHEET DATA' Button.
Finally 13 is a 'Clear All DATA!' Button
I think it's best if I give the cell location of where I would like the macro buttons located and what data they clear.
Clear DATA - Button 1 - (Is in Cell F3) - To Clear Data, Columns D,E,F & Rows 4 to 13)
Clear DATA - Button 2 - (Is in Cell N3) - To Clear Data, Columns G to O & Rows 4 to 13)
Clear DATA - Button 3 - (Is in Cell U3) - To Clear Data, Columns S,T & U & Rows 4 to 13)
Clear DATA - Button 4 - (Is in Cell F15) - To Clear Data, Columns D,E,F & Rows 16 to 25)
Clear DATA - Button 5 - (Is in Cell N15) - To Clear Data, Columns G To O & Rows 16 to 25)
Clear DATA - Button 6 - (Is in Cell U15) - To Clear Data, Columns S,T & U & Rows 16 to 25)................................
View 8 Replies
ADVERTISEMENT
Oct 18, 2007
I wrote a macro so that if a checkbox is "true" it will print and if it is "false" then the checkbox will not print. I want to do that on many other checkboxes but with this approach the number of macros will have to be equal to the number of checkboxes. I was wondering if someone here by writing one macro that will do the trick and i can assign to as many checkboxes as i want. my macro is
Sub proPaintRALPEnd()
Application.ScreenUpdating = False
On Error Resume Next
If Range("l78").Value = "True" Then
ActiveSheet.Shapes("Check Box 697").Select
With Selection
.Placement = xlMove
.PrintObject = True
End With
ActiveSheet.Shapes("Text Box 698").Select
With Selection
.Placement = xlMove
.PrintObject = True
End With
Else...............
View 2 Replies
View Related
Apr 15, 2014
I'd like to have a sheet with multiple columns of data (say A thru K for instance.). Id like to reserve column A for ONLY imputing an X. The rest of the columns b-K would have data in the cells. I'd like to have a macro that when it saw an X in column A, would copy all of the data in cells B-K in that row, paste it into the next empty row of a second sheet (for history tracking), then go back to the original sheet and continue looking for additional "X"'s and repeat. Once all of the X's were copied, it would "clear" (Not delete because some of the cells would have formulas in them that would need to remain for future use.) the cells based on the "X" then finally move all of the remaining data up to the empty rows to fill in the empty rows. This last piece would be more for esthetics to have a clean looking sheet.
View 1 Replies
View Related
Jul 5, 2009
I've reformatted the spreadsheet, and now some of the data are in merged cells.
The code lnow ooks like below, (Which I thought would work) but it doen't work ,,, and I'm not sure why
Sub Clear_Risk_Data()
If Range("J5:K5,D12,G11:H11,M11:O11") = Empty Then
MsgBox "No data to Clear."
Else
Range("J5:K5,D12,G11:H11,M11:O11").ClearContents
MsgBox "All Data Has Been Cleared", vbInformation
End If
End Sub
My sheet (An excel 2007 macro enabled version), I've just uploaded here, in case anyone needs to view it, I just don't know why it won't work?
[url]
There are just 4 boxes to clear,
Box 1,, is cell J5 & K5
Box2 is cell D 12
Box 3 are cells G11 & H11
& Box 4 are cells M11, N11 & O11
View 9 Replies
View Related
Jan 5, 2010
I have a spreadsheet that I enter data for cash flow purposes on a daily basis. At the beginning of each month I need to clear out the data containing values only as well as values beginnining with an = for eg 20000+50000+25000+74000 etc, but not formulas and text
I need the macro to clear the values , including data that has been added up as explaimned above from row 9 onwards and from column C
View 9 Replies
View Related
Jun 27, 2014
I have a macro that
- copies rows from one sheet into another sheet based on a set of criteria
- the very very very first time the macro is run, the first row of data should be put into row 7 (formatting reasons)
- however, every other time it's run it should paste into the next clear row.
But, what is happening is that, each time its run, it puts the data into row 7 => overwriting data.
I've used the offset and counter function but perhaps I need to put it in the header of the code to get it working?
My code pasted below:
[Code] .....
View 4 Replies
View Related
Nov 7, 2009
I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.
View 3 Replies
View Related
Dec 24, 2009
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
View 7 Replies
View Related
Apr 19, 2013
I have set up a macro to clear data in COl A from row 2 onwards. I need to amend the code to clear the data in the following columns from row 2 as well
Col C to I and Col L to O
Sub ClearData()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
With Range("A" & i)
.ClearContents
End With
Next i
End Sub
View 4 Replies
View Related
Aug 1, 2014
I have a spreadsheet where I am tracking several entries in a table that will keep growing. Three fields are Data Validation Drop Down Lists. The macro below works well to clear the two lists to the right when the first one is changed by the user.
[Code] .....
I want this to affect the rows below it in the table as they are added.
View 2 Replies
View Related
Aug 15, 2014
I'm having a problem with a macro clearing a formula in a cell. I have the same type of cell that doesn't have the problem but I can't find the difference between the 2 cells or difference in vb that's making it happen. I have to intentionally cause this to happen but don't see why it's happening. Do I need to attach workbook and describe what's happening? I have been copying and pasting from different sources as well as paying to have it created/started but it was expensive(for me) and I make nothing off of it, just use it at work. I am not proficient in Excel or vb but I'm desperately trying to learn as I go so as not to fork out a few hundred dollars again.
here's atleast the vb for the macros:
VB:
Sub clearclientinfo() '
'
'
'
Sheet5.Select
[Code].....
View 9 Replies
View Related
Dec 4, 2012
i have the following macro when cell value in column "K" changes, it distributes the data accordingly.
however, the issue is, if i change the value in K (which is a date), so another value, it leaves the distribution in tact....i want to apply "clear content" and then make the macro re-apply.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) = "$K$" Then
Dim srt As String
[Code].....
View 9 Replies
View Related
Nov 30, 2006
I have data in columns a,b,c and d from row 1 to about 200 right now. Every week another 9 rows are added. I wanted to create a macro that will delete the last 9 entries in each column.
View 9 Replies
View Related
Aug 20, 2008
I'm trying to figure out code to have only the contents of cell 'a2' cleared when the contents of cell 'a1' is changed. The issue is that both cells are derived from lists and I don't want the formats cleared.
View 9 Replies
View Related
Jun 5, 2009
if I had text in column A, I would like to clear all the cells to the right and leave the text in column A.
Also, if I had text in column A and B, I would like to clear the cells to the right.
View 9 Replies
View Related
Sep 30, 2009
I need code for a macro that will find a min value in a particular column and based on that min value clear the contents of other cells for that min value.
For example, I have dates in column E such as:
200907
200906
200902
200809
200803
200710
200707
Therefore, I need to find all records for 200707 and clear the contents in other cells.
I can't hardcode the min date because that can change from month to month when new files are created. As can the number of rows.
I'm using Excel 2007.
View 9 Replies
View Related
Jul 18, 2006
I have a protected worksheet in which certain calculations are performed based on certain inputs.
For example, let's take Column E:
Starting in cell E4, I have cells in that column which are input cells and further down the column are calculated cells which are locked and whose formulas are hidden.
I was looking for a macro which would go down col E and clear contents of all unlocked cells without messing up any of the protected cells.
Also, there are certain cells in which I want to clear contents and some cells that I want the macro to insert a "0" value.
The input cells are non-contigous.
View 7 Replies
View Related
Jul 21, 2007
I need a macro that will clear out all zero's in columns D8:O?
The amount of rows vary, but should never exceed 1000. I'am not wanting any cells to shift. Just the contents deleted.
In other words, if e5 contains the value 0, I need it to not contain anything.
The exception is the total row.see file for example.
View 7 Replies
View Related
Oct 25, 2012
I have an month input in cells B2 (user can enter values from 1-5)
I then have a data table that has month 1,2,3,4,5 running across range G9:K9. the data is held in range G10:K19.
So if user types in 4 in B2, what should happen is that months 1,2,3 and 4 the data for these months should be cleared and data in month 5 moves forward into month 1.
So basically, anything left of the month entered is B2 should be cleared and replaced with anything right of the month in B2.
1
2
3
4
5
£11
£133
£29
£193
£100
[Code] ........
View 3 Replies
View Related
Dec 16, 2008
i am trying to lookup up when ppart matches spart and subtract total shipped from total produced. Here is where I am getting stuck .... because spart has multiple returns LOOKUP(2,1/((I2=C:C)),J2)
View 3 Replies
View Related
Apr 6, 2009
In Row 3, starting at cell C3, I have a list of Stores in the format Store A (Town 1), Store A (Town 2), Store B (Town 1) etc
In Rows C4 to CA7 I have 1 or -1
I want to count the number of instances of 1 for Store A
I have tried the following formula, which is returning 0
{=SUM((C3:CA3="Store A *")*(C4:CA7=1))}
View 3 Replies
View Related
Jul 14, 2009
Which is the best way import many (thousands of) files in text format into a single excel spreadsheet. Importing files with e.g. 50 lines to create 50 columns for rows for every file
View 2 Replies
View Related
Feb 19, 2010
I'm trying to create a workbook that will calculate times. Currently I have a worksheet that has, for example Incident #1, Date/Time In, and Date/Time Out. At the end of the row it will calculate total time in hours.
I need to create a second worksheet that will have Incident # to correspond to the incident # on Sheet1, with Date/Time In and Out. However, on this sheet there could be more than one occurance of Incident#1, and multiple times in an out. That total will be deducted from the total of incident#1 on Sheet1.
The easiest way to explain this would be...say for the sake of argument Sheet1 calculates the duration of a vacation in hours (lets say 168 hours). Sheet2 will calculate how much of that time was spent doing business (lets say 2 hours on day1, 3 hours on day 4). In the end I want to subtract 5 hours from 168 hours giving me 163 actual vacation hours.
My problem is, I don't know how to go about using the values in multiple rows in Sheet2 that correspond to the row in Sheet1.
View 6 Replies
View Related
May 8, 2012
I need to apply filters based on 2 criteria
Filter1Dropdown SelectionFilter2Dropdown SelectionHeader1Header2Header3Header4Header5Header6
Depending on what is selected in Filter 1, lets say it applies to column F and i get a data set
Now with the filtered dataset of Filter 1, I should be able to apply Filter 2 or vice versa.
Also at the end, I need to release all filters so that i can see the entire data set.
View 2 Replies
View Related
Jun 9, 2009
I am having a hard time with this one. I have 3 cells.
A1 Dallas, TX.
A2 Chicago, IL. (Sometimes Blank)
A3 New York, NY
I would like this info to transfer to another cell like this
Dallas, TX / Chicago, IL. / New York, NY (All in one cell with the "/" to divide.
When the "Chicago, IL." is missing it would look like
Dallas, TX / New York, NY
View 9 Replies
View Related
Sep 22, 2007
i have created that will sort a db into dozens of sheets, now i need to be able to reset the process. There are 3 sheets i need, "balances", "trans", and "template". im unsure as to how define the sheet names so that the dSheet variable will recognize them. the way i have it now just deletes everything.
Sub reset()
Dim dSheet As Variant
Sheets(1).Select
On Error Resume Next
Do Until Sheets(1)
dSheet = ActiveSheet.Name
If Not dSheet Is "balances" Or "trans" Or "template" Then
Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True
Else
ActiveSheet.Next.Select
End If
Loop
End Sub
View 3 Replies
View Related
Feb 25, 2014
This macro clears all the contents of each sheet to which it refers, however, after putting it to use I find that I need it to clear only columns A-G starting at row 10. I am not sure how to modify it to do so though. Actually it would be even better if it would only clear columns A, B, C, E, F, and G.
View 14 Replies
View Related
Oct 14, 2008
when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.
View 2 Replies
View Related
Jan 26, 2009
My Workbook with a macro to clear ONLY the highlighted Cells.
The Highlighted Cells can be found on the sheet "Line 5"
View 11 Replies
View Related
Jun 19, 2009
I am trying to create a marco that clears the columns of a named range if the sum of the cells in the range is zero.
View 3 Replies
View Related
