Cell To Clear Contents Of Group Of Locked Cells?

Feb 22, 2013

I have created a worksheet with many calculations based on user input into several unlocked cells. I would like to create a cell that when selected, would clear the contents of this group of unlocked cells.

View 10 Replies


ADVERTISEMENT

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 Contents In Specific Cell And Cells In Next 2 Columns To Right?

Jul 3, 2014

I am currently using this Code to search column1 for a Key Word that is entered into textbox "Kunde" on my userform.

[Code] .....

What i would really like to be Abel to do is :

Search for the cell in column1 with =Kunde.Value

Clearcontents of this cell and the 2 adjacent to the right.

i.e. Word is found in A7

Cells A7:C7 contents are cleared and fill Color returned to default colorindex 0

View 3 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

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.

VB:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
ActiveSheet.Unprotect
'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

Conditional Clear Contents Of Cells

Oct 10, 2008

I have a dropdown list in the range E17:E91 containing a list, and two of the values in the list are "DI" and "LTC". What I need to happen is if either of these are selected, the corresponding cell in column F needs to have it's contents cleared. Ex. if in E17 the value is ever either DI or LTC, cell F17 gets contents cleared. if column E contains ANYTHING ELSE, nothing happens. That's all, I've taken care of conditional formatting and data validation already. So even if data is pasted in the worksheet into column E it needs to recognize that and always act in real time.

View 14 Replies View Related

Clear Contents Of Cells In A Range, LEN=0

Dec 23, 2009

I'm using a macro to copy the results of a formula and paste the values only on another sheet. The result includes lots of "blank" rows. I have another macro to get rid of the empty rows and move the information up.

It's not working because the "blank" rows aren't empty. Even though I paste values only, experimentation shows that the cells that appear blank return a false to the ISBLANK test with a length of 0.

So now I think I need a macro to run after the pastespecial command to look for cells within a range with a length of zero and delete the contents of those cells, but leave alone anything with a length of >0.

I am brand new to the idea of using VBA, but I have successfully cobbled together some stuff and can usually modify things to work.

It seems I need to maybe use some sort of IF statement along with a LEN and ClearContents. I don't want to delete the blank cells, just make them truly empty so that all of my actual data stays where it should, and my delete empty rows macro works correctly.

I did a search and see some info on clearing contents of columns or rows, or clearing contents based on the content of other columns or rows, but I'm unsure of how to tell it to search each cell within a range and clear the contents of 0-length cells to make them truly empty.

View 10 Replies View Related

Clear Repeated Cells Contents

May 18, 2007

how could i clear repeated cells contents of one column with code suposing that there are some blank cells between the first empty cell and the last?

View 8 Replies View Related

Clear The Contents Of All The Cells In A Certain Area Of The Worksheet

Dec 8, 2008

I’m working on a macro to clear the contents of all the cells in a certain area of the worksheet that are not locked and do not contain a hyperlink.

(C7 is the first data-entry cell; EndPage1 is a specific cell near the bottom right of the sheet that sets the outside range of the cells to be cleared. This is the code so far:

View 2 Replies View Related

Clear Contents & Change Color Of Cells

Nov 3, 2006

how to clear contents and change colour index of cells.

This is for a booking system. The current VB codes allows bookings to be made by entering a reference number, dates of bookings and room type and bed type. When the VB codes are executed, the cells corresponding to the details entered earlier, would fill the booking table with the reference number and change the colour fill (e.g. yellow) to indicate that the slots are booked. Please refer to attached sample.

However, I do not know how to delete the booking. I would like to assign a cell for user to enter the reference number that needs to be deleted. So when the reference number is entered into that cell, user could execute a function which will clear the contents and change the colour index to none for that particular booking with that reference number(s).

Attached is a sample of the problem.

View 9 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

Protecting Formulas In Cells From Clear Contents Command

Sep 19, 2007

I have a worksheet from which I regularly clear all contents by selecting all the rows and selecting 'Clear Contents' from the right button menu. However, I now have added a formula in Col Y that I want to protect. I know I could select all columns up thru X, but that would clear the R1 headers, so I want to avoid doing that. I can always work around, but I'm curious, is there a way to clear only values and thus protect the formulas when using the Clear Contents command?

View 9 Replies View Related

Excel 2010 :: Clear Contents Of A Range Of Contiguous Cells

Jun 4, 2013

I am trying to clear the contents of a range of contiguous cells (containing formula) in Excel 2010 64bit. But it is taking a LONG time (read >30min to clear a range 288 x 100). Originally was doing it in VBA but it was taking too long so I started stepping through the macro and identified that the slow down was occurring on a particular sheet only. So the problem is in Excel....not VBA because it is still very slow when deleting the same range in Excel without using VBA.

I've tried all the usual suspects including: Application.EnableEvents = False (run from the VBA Immediate window when using Excel without VBA), Setting to Example of slow clear contents5.xlsxExample of slow clear contents5.xlsxManual Calculation, Turning off screen updating, deleting all the conditional formatting on the sheet, removing any data validation from the sheet (yes all these at the same time). There are no links to external workbooks. I've even tried in VBA changing from .ClearContents to .Value = vbNullString. All to NO AVAIL! By way of background the file is about 72Mb #.xlsm.

If I try to clear contents of larger ranges on other sheets in the same workbook it is instantaneous. If I copy the problem worksheet off to a new workbook, I can delete the problem range almost instantly. Why deleting a range of cells should take so long! Again to clarify, I am using the terms "delete" and "clear contents" interchangeably, but they both mean "clear contents" (not delete and move up cells). P.S I've also looked into the reported conflict between Excel and Google Desktop Office Add-in (Office Button->Excel Options->Addins->Com Addins and deselect Google Desktop Office Addin) but I didn't have Google Desktop Office Add-in so it isn't that.

View 6 Replies View Related

Clear Contents Of Cells Based On Data Validation Selection

Apr 13, 2012

I am trying to find something that would allow me to have a spreadsheet clear the contents of a certain cell based on the selection made from a data validation list which resides on the same row. This can best be described with an example.

The user selects anything but " " or "none" from the validation list, which for this example resides in C5, would trigger code to clear the contents, if any, in AA5. If " " or "none" is chosen the value remains.

This capability should be available for each row through 100.

View 3 Replies View Related

How To Clear Cell Contents

Jan 17, 2012

I have the following code where I want to clear the cells.

Code:

Workbooks(strOutputFileName).Sheets(2).Activate
Range("F9:F25,C27:E57,F59:F60,J9:J25,G27:I57,J59:J60,N9:N25,K27:M57,N59:N60,R9:R25,O27:Q57,R59:R60").Select
Selection.ClearContents

When I run this code, where it should select G27:I57, it instead selects G27:N57!!!!

When I manually try to select the G27:I57, the selection jumps to the right to N57.

When I use this piece of code on a different worksheet, it works OK!

View 6 Replies View Related

Clear A Cell's Contents

Mar 7, 2007

Is it possible using vba code to clear a specific cell's contents? I have a workbook that has text boxes that need the linked cell cleared when the workbook opens so the user can start fresh with empty text box's.

View 6 Replies View Related

VBA Clear Cell Contents If Criteria Met?

Feb 7, 2013

I have a spreadsheet with data to row 5000. I have column Y that has an "X" in it. I would like some VBA code to look at each row up to 5000, in column Y for the "X". If it is there, clear the cell contents on the current row in columns T, U, and V.

I've tried modifying some existing code (excluding the Y column range of 5000) but keep getting a "Compile error: Wrong number of arguments or invalid property assignments". How do I set the 5000 limit and get this code back on track?

Sub RemoveBankDelay()
n = WorksheetFunction.CountIf(Range("Y:Y"), "X")
For i = 1 To n

[Code]....

View 9 Replies View Related

Automatically Clear Contents Of Cell When Selected

Oct 13, 2009

Is there way to automatically clear the contents of a cell when the user clicks in that cell. Eg, click in "A1" then then "A1" clears automatically.

View 10 Replies View Related

Clear Cell Contents Based On Defined Name

Jan 13, 2010

I'm trying to clear cell contents based on a defined name given to a set of various cells in my worksheet. The cells are not continuous, but since they're given that defined name, I don't think it matters.

The defined name is listed as "CommentsFields".
The worksheet name is listed as "QPRForm_V6"

I found a similar thread, but cannot get it to work with what I'm looking for. I'm relatively new to writing VBA/macros, so I'm not sure what to do.

View 6 Replies View Related

Code To Clear Cell Contents For Rows?

Sep 12, 2012

Small piece of code to clear the contents of

Rows 33 thru 100 , then
Rows 142 thru 209 , then
Rows 251 thru 318 , then upto
Rows 43524 thru 43591 then finish

View 5 Replies View Related

Automatically Clear Contents Of Cell Range

Jan 17, 2013

Is is possible to clear the contents of a cell range if a message box appears stating an error. I have tried the following code but the logic doesn't actually work and am struggling to achieve what I am trying to do.

My code so far is as follows:

Code:

If ActiveSheet.Range("F84") > 0 And ThisWorkbook.Worksheets("PES").Range("D24") = 0 Then _
MsgBox "Your Entitlement is currently 0", vbCritical, "Error"
ActiveSheet.Range("K84:T84").ClearContents

The message box appears fine if the IF statement is true but if IF statement is false, the cell range of K84 to T84 on the current sheet is still cleared..

View 2 Replies View Related

Clear Contents Based On A Selection In Another Cell.

Feb 21, 2007

If I have a pick list, or drop-down list, in cell B21 and one of the options in that list is "Clear", how can I get the contents of cell C21 to be cleared when I select the "Clear" option in B21?

Constraint... Don't want to use VBA.

I am thinking of hiding a formula somewhere other than in C21 that evaluates B21 and does an If Then kind of deal to clear C21.

View 9 Replies View Related

Clear Contents If Cell Doesn't Begin With #

Sep 3, 2008

I am trying to write a piece of vba that clears the contents if the first character in the cell is not #.... if I use "#"*, I get a syntax error

Just figured my syntax needs a slight adjustment to be recognized...

View 9 Replies View Related

Macro To Clear Contents In Every Other Cell Of A Selection

Apr 25, 2008

I need to clear the contents of every other cell in a selection of 5000 rows starting with the first cell. Is there a macro that can do this for me?

View 3 Replies View Related

Clear The Contents Of Each Cell In A Range Without Losing Formulas

Dec 6, 2006

Is there a way to clear the contents of each cell in a range without losing formulas?

View 9 Replies View Related

Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell

Feb 18, 2009

Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.

View 2 Replies View Related

Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?

Jun 1, 2014

i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows

View 3 Replies View Related

VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R

Mar 9, 2013

I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.

I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..

View 9 Replies View Related

Clear Contents Of Cell Based On Comparsion Of Cell Values

Mar 7, 2007

I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col

if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected

Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell

I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.

Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................

View 4 Replies View Related

Hide Column And Clear Contents Based On A Cell Value In Another Sheet

Mar 19, 2009

I have a workbook with two sheets of data. I want to hide column B of Sheet2 and clear contents of range B2:B50 if the value in A1 of Sheet1 is "a".

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved