Macro To Clear Cells

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


Macro To Clear Certain Cells In Row If First Cells Date Is In The Past

Apr 9, 2014

I would like to run a macro that would check if the date in the first cell of row is in the past.

If yes, then it would clear contents of cell in B,C,F,I,L of the same row (PLAN + SHIFTS). Or even better it would set them to 0 (zero).

Then it would hide the entire row. Is this even possible?

Please see the example: Excel.JPG

View 8 Replies View Related

Macro To Clear Cells With Numbers But Not Cells With Formulas

Jan 22, 2013

Macro to clear cells with numbers but no cells with formulas with in this macro:

Dim i As Long
i = Range("E3")
If i > 0 Then
' Copy range
Range(Cells(6, 10 + i), Cells(500, 17)).Copy
Range(Cells(6, 10), Cells(500, 17)).Select
' Paste special
ActiveSheet.PasteSpecial Format:=2, Link:=1, _
DisplayAsIcon:=False, IconFileName:=False
' Clear i columns on the right
Range(Cells(6, 18 - i), Cells(500, 17)).ClearContents
End If
End Sub

The range is where the cells with numbers need to be cleared but not the ones with formulas.

View 2 Replies View Related

Clear Cells To Right IF Macro

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

Macro Button To Clear Certain Cells

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

Create Macro To Copy Sheet - Clear Cells And Add A Day

Jul 8, 2013

I am looking to create a macro to copy sheet Day(1) and call it Day(2). I would like Y3 to increase one day with the date, I would like Y4 to increase day and 1 and clear data from G35-G65.

View 4 Replies View Related

Macro To Clear Contents Of Cells When One Cell Equals To 0 %

Oct 8, 2013

I'm trying to create macro that will clear the contents of the cells in the 4 columns to the left of a cell that = 0 %, as well as the cell that = 0%. For example, if cell F13 = 0 % then the contents of B13:F13 would need to be cleared. The range in which data is being pasted into is B13:F27.

View 4 Replies View Related

VBA Macro: Clear All Constant Cells In Range That Don't Have Formulas

Jun 22, 2009

is there a way to do this in VBA? I've also read: - Dave Hawley's recommendation of using: Sheet1.UsedRange.SpecialCells(xlCellTypeConstants).ClearContents

from another thread (which is excellent!)

- SHG's recommendation of using a named range, for example:


Given my limited knowledge of VBA, how would I now combine the two to write a VBA sub-routine that clears a named range entitled "Entry" on a sheet entitled "Data"? Would the following be the correct syntax: Worksheets("Data").Range("Entry").SpecialCells(xlCellTypeConstants).ClearContents

View 6 Replies View Related

Clear Macro NOT Clear Formula?

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:

Sub clearclientinfo() '


View 9 Replies View Related

Excel 2010 :: Create Macro Button To Clear Multiple Cells On Multiple Worksheets?

Jul 2, 2014

I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.

On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells

B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42

On all other 'Caravan sheets' I want to clear the contents of cells

D4 & D5
E4, E5, E22, E23, E41 & E42

It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead

View 8 Replies View Related

Clear Contents Of All Unlocked Cells (many Are Merged Cells

May 28, 2009

I am looking for a code that will clear all of my unlocked cell in sheet 1. That is not a problem but since many of the cells are merged I know it keeps throwing me an error saying cannot change contents of merged cells or something like that. Does anyone know how to get around this without unmerging the cells. I saw a code to unmerge all of the cells on a sheet but I really don't want to do this as I already have worked around most of my problems with the merged cells.

View 14 Replies View Related

VBA To Clear Contents Of Cells And Keep Some Cells Locked

Jan 25, 2010

I have written this code to clear the contents of certain cells, lock the content of others and protect the sheet again it works on sheet1 but not on sheet 7. This is suppose to happen when the Print button on my sheet is clicked.

View 4 Replies View Related

Clear Cells In Column Where 2 Cells In Same Row Are Filled

Mar 30, 2008

how do i create a formula or macro that will: Clear the contents of Cell C1 If Both Cell A1 And B1 Are Filled. I will need to check all the cells in column A,B and C.

View 2 Replies View Related

Clear #VALUE! From Cells?

Apr 16, 2009

I am getting a #VALUE! error in one of my cells. I know it is because my worksheet is missing information to complete the formula. The information will be inputted periodically in the worksheet (lets call it worksheet number one). But, I have the results of cells in worksheet one that are tied to worksheet number two. Worksheet number two is getting the #VALUE! also until worksheet number one is completed (periodically).

But, in worksheet number two, I have an auto sum that adds up the results from worksheet one so I am getting the same #VALUE! error on the auto sum because only a few worksheet from worksheet one are complete. Is there anyway to clear the #VALUE! and get a number in the auto sum even if only a few worksheets from worksheet number one are inputted?

View 4 Replies View Related

Clear Cells Using VBA

Apr 25, 2002

I need to clear all cells in a column that have a particular value. The value is sometimes a constant like "0" (zero) and sometimes is a value based on a formula. Is there a VBA code that will allow me to select those cells in column matching a particular value and clear them?

View 9 Replies View Related

Clear Contents Of Cells

Sep 10, 2013

I want the function of clear contents to work based on the value of B4. If I run the code with clear content part excel crushes.


Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
'Range("B10,F10,H10").ClearContents ''*** if I run this part the excel crushes ***'''
Range("B10,F10,H10").Locked = True

[Code] .....

View 4 Replies View Related

Clear Cells Containing Ellipse

Apr 16, 2014

I have a spreadsheet with a large table of data (values only no formulas). Whoever programmed it, they decided that cells with irrelevant entries should display three dots, i.e. "...". Problem is now need to clear these cells (so they are truly blank) but some of them have been auto-corrected in to ellipses and some have not. I wrote the following code:

[Code] .....

This is fine for the three dots that are just three dots, but for the ones that were auto-corrected to ellipses it doesn't work. How do I amend the code so that it clears the cells that are three dots and the ellipses too?

View 2 Replies View Related

Clear All Unprotected Cells?

Jul 25, 2014

I have a button, and I need a Macro that will cause it to clear all unprotected cells. I think I can figure out how to get it to clear an individual cell, and I suppose I could enter every cell I want cleared individually...but that might take awhile.

Is there a way to set up a Macro that will delete the contents of every unprotected cell? I guess I haven't tried it yet - if I protect the cells, will it not delete those? Also, there is a small image - I don't want that deleted either.

View 8 Replies View Related

Clear Merged Cells

Oct 8, 2012

I have a spreadsheet that has merged cells on that is typed into.

I have a Macro that pulls the information into another sheet and then it tries to clear the cells for the next person to fill in. The issue I am having is that i cant clear the merged cell. I have tried the following code

Cells(18, 2) = ""
Range(Cells(18, 2), Cells(26, 11)).ClearContents

Sheets("Coach").Cells(18, 2).ClearContents

View 5 Replies View Related

Clear Non-blank Cells

Jul 6, 2008

The best script to clear all non-blank cells, in my case from Cells(7,1) forward. I currently use this:

.Range(Cells(7, Columns.Count), Cells(Rows.Count, 1)).Value = ""

Will this scipt take a lot of memory to execute? Is there any other method?

View 9 Replies View Related

Clear Cells With Specified Style

Jul 4, 2007

I have a spreadsheet wherein the defined data input cells are formatted using styles, i.e. data input style. I would like to know how to select all cells formatted with that style within a spreadsheet (whether worksheets are visible, hidden or very hidden) and delete the contents of those cells while still keeping the cell style.

View 9 Replies View Related

Clear Non-contiguous Cells

Jul 23, 2007

Am looking for routine that can be used to delete the contents of cells from multiple, non-contiguous cells throughout a single worksheet. I would like to place a "button" or some form of toggle-switch on the worksheet that will clear all entries at once. I see a thread "Macro Deleting Wrong Cells" that references a VBA sub-routine called "DelCells". I happen to be running Excel 2007 and am still adjusting to the new menu "structure" :-)

View 5 Replies View Related

Clear All Worksheet Cells

Oct 10, 2007

Gotta question about destroying the contense of a worksheet. If for instance I got a macro which uses a different source everytime I run it, and this macro gives a result in a result worksheet. How can I possibly delete the contense of the result worksheet automatically before the execution starts? What I mean is: without selecting all the cells and pressing delete or either using a for loop to go through all the worksheet cells. Is there any function which clears the contense of a worksheet? (without deleting the worksheet itself)

View 2 Replies View Related

Clear All Cells On Another Worksheet

Feb 22, 2008

I have the following code in a Excel 2003 spreadsheet with hope that I can clear contents of unlocked cells in one excel file from the code stored in another Excel file.

' Macro recorded 02/21/2008 by Steve Keene


I get the Subscript Out of Range error window when it hits the first line of code.

I've reviewed this via searching for other posts, but none seem to solve the problem directly.

View 3 Replies View Related

Add Clear Content To Macro?

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.


Private Sub Worksheet_Change(ByVal Target As Range)
If Left(Target.Address, 3) = "$K$" Then
Dim srt As String


View 9 Replies View Related

Macro To Clear Contents.

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

Clear Data Macro

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

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

Macro For Min Value Then Clear Contents

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:


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

Macro To Clear Data

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

Clear Contents Macro

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

Copyrights 2005-15, All rights reserved