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
ADVERTISEMENT
Oct 16, 2009
I'm putting together a time sheet that can used to calculate total hours worked by department etc. I plan on protecting the worksheet and only allowing certain cells to be selected and data entered.
Is there a way using VBA to place a button on the first spreadsheet that will clear all cells that are 'unlocked'?
View 6 Replies
View Related
Jun 18, 2007
is there a way in VB to make it fast, and tidy, clear the contents of all unlocked cells on one sheet ?
View 4 Replies
View Related
May 29, 2014
I wish to copy a merged cell (3 cells) based on if only 1 of 3 cells to the right contain "X". if the top cell does not contain "X" than the merged cell is not copied. Also, is therea more elegant to copy 3 columns at a time rather than do one at a time as my code shows:
Sub CopyICUCAPU()
'
' CopyICUCAPU Macro
'
Dim i As Integer
[Code].....
View 14 Replies
View Related
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
Code:
Sheets("Coach").Select
Cells(18, 2) = ""
Range(Cells(18, 2), Cells(26, 11)).ClearContents
Sheets("Coach").Cells(18, 2).ClearContents
View 5 Replies
View Related
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
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
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
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
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
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
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
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
View Related
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
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
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
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
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
Jan 31, 2014
1.I need to protect certain locked cells from editing and allow certain unlocked cells to be changed on multiple worksheets.
2.When all of the changes are made to the unlocked cells, I need to password protect the entire workbook (except one worksheet) from any changes. (i.e. Prevent even the unlocked cells from being edited)
3.I also need a password to un-protect the workbook and return it to the state described in # 1. above .
View 1 Replies
View Related
Apr 8, 2008
i have a worksheet which is a form which feeds into another worksheet which acts as a database. i have a button which clears the form based on code which is essentially "clear contents".
the problem is i have a few cells where i would like to retain the formulas. i tried to do this with custom in validation but this did not work. i also tried to enter the code directly into the worksheet but this didnt work either (my skills are limited..). i am avoiding using protect sheet bc that in turn will affect many of my other buttons. is there something i am overlooking?
View 9 Replies
View Related
Mar 21, 2014
I have a spreadsheet that has mostly locked cells but randomly placed unlocked cells.
The sheet is completed, often saved and printed. We then go back into it and use it for another customer calculation and need to ensure all previous entries are removed. With the locked cells you can't simply highlight an area to clear so I am wondering if there is a way to clear the unlocked cells in one step other than entering on each on to clear.
Would there be any way that if we cleared the first cell the others would clear?
View 4 Replies
View Related
Oct 4, 2012
I'm looking for a macro that only selects the unprotected cells in a range. So that I can copy a formula in these cells.
I found the below macro on the web but it will select all the unprotected cells in the worksheet. I guess this one can easily be adjusted to only select the cells in a range, but I do not know how to do this.
Code:
Sub SelectUnlockedCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
[Code]...
View 4 Replies
View Related
Aug 1, 2006
I have a macro which when run firstly unprotects the worksheet, makes a change,then protects the sheet again with line of code below.
Sheet.Protect Password:="whateveritis"
What do I need to add to that to ensure that it allows selecting unlocked cells only.
View 2 Replies
View Related
Mar 12, 2007
Is there any code to change unlocked cells to locked..
I have worksheets for each month which are protected with unlocked cells. At the end of the month I need to go into the worksheet unprotect it and run a macro to change the unlocked cells to locked so I can protect it again. This is to preserve the data entered into the cells.
View 9 Replies
View Related
Jun 17, 2007
I am trying to select all unlock cells in the active sheet. However, my method checks through each cell in the sheet and it takes too long.
Sub SelectUnlockedCells()
Dim rng As Range
If IsNull(Cells.Locked) Then
'if null then there is unlocked cells
For Each c In cells
If c.Locked = False Then
If rng Is Nothing Then
Set rng = c
Else
Set rng = Union(rng, c)
End If
End If
Next c
Else
End If
If rng Is Nothing Then Exit Sub
rng.Select
End Sub
View 9 Replies
View Related
Dec 16, 2009
I have had a workbook that has 2 sheets.
When I protect the main sheet with a password, the unlocked cells are also locked.
When I move my cursor over any cells, it has the pointed finger as though it has a macro over it, which it doesn't.
When I click on any cell, the screen flashes and a large part of the screen momentarily it looks like a selected area.
I also am using Freeze Panes. Removed the Freeze and still no good.
If I use the horizontal scroll bar and click the right arrow 3 times, they become unlocked.
If I move the screen 3 clicks to the left, it re-locks all of the unlocked cells.
View 9 Replies
View Related
Feb 19, 2012
I want to utilise an artificial protection by keeping a cells value constant even though it can be selected, deleted, edited etc.
Is this possible? I want to make a sheet where the cursor isnt visible except on certain sets of 4 cells.
My method being leave the 4 cell sets unlocked and lock everything else and then protect the sheet.
This means only the 4 cell sets are selectable/editable but I only want them to be selected (as this triggers events) but I dont want their values to be changeable. The values will always be 0,1,2,3
View 9 Replies
View Related
Oct 5, 2006
Ok I've spent over 2 hours reading MANY peoples issues with this "BUG". Back in 2003 was the earliest... no one has an answer that works.
When you protect a worksheet and only have UNLOCKED CELLS selected...somehow through loading and unloading the file... you can select locked cells....
I can not find a pattern but many people have had this issue all with no concrete answers.
Just by loading and saving, exit and loading and saving, exit and loading.... i can now select locked cells that I previously couldn't. (I can't do anything as it's still protected...) It's a pain because it wrecks my tab flow.
View 9 Replies
View Related
Dec 6, 2007
I have a workbook with a series of sheets that have tables for entering data. The table row and column labels and formulas in certain cells of each sheet are locked and some sheets are hidden.
When opening the file I want to clear all cell contents (interior color, comments, data, etc) in all the unlocked cells on each sheet that is not hidden.
I tried protecting the sheets first so only the unlocked cells would be accessible, but when I run the code below I get an error saying that the clear contents etc. cannot be performed because the sheet is protected. If it is not protected, everything gets wiped out.
How can I keep the locked stuff but clear the unlocked cells?
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
ws.Protect Password:="aaa" 'Protect each sheet
ws.Cells.ClearContents 'clear content of any unprotected cells
ws.Cells.ClearComments 'clear any cell comments
ws.Cells.Interior.ColorIndex = 0 'set background colour to no fill
Active.Cells.Range ("a1") 'make the active cell the top left
End If
Next ws
View 9 Replies
View Related
Apr 13, 2009
I want to do is clear all the cells on a worksheet that are not locked. Problem is that any cell could be locked, no rhyme or reason.
View 9 Replies
View Related
