Unlock columns if Column is not blank in any row
Using VBA
On Sheet("Draft Final") , search every row and
If Col A is not blank, then unlock Columns G: M and unlock Columns O:U
I have a worksheet with active range A9 thru K200 that is locked. When worksheet is opened, I need it to automatically unlock all rows that are blank, for users to input data. It would be great if this could also require data in column A, C and K before allowing workbook to be saved and closed.
View 2 Replies View RelatedIs it possible to unlock Blank or Empty cells within a Range?
" for example; all cells in the range (A1:F10) > 0 .locked = False "
I have some code from something simmilar i have added below:
Sub test_Click()
Dim rngTemp As Range
For Each rngTemp In Range("grey").Cells
If len(rngtemp.value) > 0 Then
LockMatchedCells rngTemp, Range("against")
endif
Next
End Sub
I need to lock cells or ranges in a worksheet which has value (any value)....
The cells which are blank should be unlocked so that the users can enter data.
I have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).
I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).
The issue is, every time the workbook is opened this protection must be in place.
However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.
if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.
There is no need to password protect anything.
I just want to Hide all the blank columns on the sheet apart from column A.
I already have this code, just dont know how to alter it:
VB:
Sub HideEmptyColumns() Dim c As Long
Dim n As Long
Application.ScreenUpdating = False
[Code].....
Any way to conditionally unlock and unprotect certain cells in a column. I have been unsuccessful with my attempts at customizing other people's vba code that is a little different than what I am trying to achieve. So here's what I would like to accomplish:
If A23 = "Please See DME", then unprotect (pw=richie13) and unlock D23, then protect (pw=richie13) sheet again
If A24 = "Please See DME", then unprotect (pw=richie13) and unlock D24, then protect (pw=richie13) sheet again
If A25 = "Please See DME", then unprotect (pw=richie13) and unlock D25, then protect (pw=richie13) sheet again
and so on and so on.
I am doing a lookup, populating a column (which works although slowly) then (in this part) doing a search on the header row.. If the heading contains the word "Category", I wish to insert a blank column next to (the column containing the heading), colour it yellow then look for the next heading and repeat if found.
It appears to clear my first row so I must have my columns referred incorrectly as rows
Dim FWord As String
Dim i As Integer
Dim lCol As Long
Dim MyString As String
.
.
FWord = "Category"
lCol = Range("A1").End(xlToRight).Column
For i = 1 To lCol
Cells(1, i).Value = MyString '
I have an inventory log that requires multiple cells in different columns to be unlocked based on a reference cell's input.
So, if a cell in column E has "MORNING" entered then cells L/M/N are unlocked and said user can input data for that row, and only that row. If anything else is in E, then L/M/N are left locked.
Is there a way to do this without coding, just using regular IF() in the cell directly; IF(ISTEXT(E3)=MORNING, Unlock, KeepLock)? I know that's nowhere close to being a legitimate statement, but it's the best way I can translate my thoughts.
I got 3 columns of cell values as follows:
Demo 1
COL A COL B COL C COL D
_ 12 14 16
_ 32 12 18
_ EA LW RA
Demo 2
COL A COL B COL C COL D
12 12 14 16
32 32 12 18
EA EA LW RA
14
12
LW
16
18
RA
Can data of the 3 columns be copies (by vba code) to the blank col A in the exact sequence as shown in Demo 2?
I am trying to create a sheet on docs.google.com that takes the sum of 1 row divided by the sum of another row removing numbers from the denominator if fields are left blank. Hard for me to explain so here is an example:
Counter
Column 2
Column 3
[Code]....
Leaving a blank value in column 2 makes it read 4/6 giving me 66%, Column 3 giving me 50%.
What I would like it to do is if there is a blank value in any column remove column one from the denominator.
Upon completion the total output percentage for column 2 should be 100%, and column 3 should be 75%.
I need a macro to find the last cell in the column, then copy the formula to the next blank cell. Then, it goes back to the last cell (above) and paste's values. Then, go to the next column and repeat the process. I can do this but have to call each cell separatly...however, I would like to do it in a loop to simplify things. It would be great to even be able to just set the start and ending columns. Here is my current code:
Dim rng As Range, aCell As Range
Set rng = Range("C8, D8, E8, F8, G8, H8, J8, K8, L8, M8, N8, O8, P8, Q8, R8, S8, T8, U8")
For Each aCell In rng
Selection.End(xlDown).Select
Application.CutCopyMode = False
[Code] .......
It does not go to the next column, instead it stays in the same column and repeats the process.
I have an excel file and what i want is to sum two different columns to a third one, however there are some important notes:
1. Column A has dates
2. Column B has numbers f.e. 25, 30, 35 etc. , which have to be added to dates of column 1 , so a new date will be created in the new column
3. Colums A and B are not next to each other.
4. Some cells of column A and column B are blank , actually columns are sth like this
A B C
5/2/2014 25
blank blank
8/5/2014 35
10/9/2014 30
blank blank
When a date value occurs in a cell of column A , then always a number will occur in column B
When a cell is blank is column A , then the respective cell of column B will also be blank
What i want is to add values of column A and B to create column C automatically ( each column has 1000 cells ) , however in case of blank cells , i want a blank cell in column C, not something like #####
Apart from this , i want in the future , when i expand column A and B , when values are entered there, i want column C to be created automatically.
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.
I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.
Note: Any row headers will always be in row 1 only
Current...
The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.
I'm not sure how to say leave the result blank if the column index number is blank.
Excel 2010
Userform = Tab 1
Database = Tab 2
I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.
I need to move part columns of data from 6 columns to form 1 large column in column A.
For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !
Also need to delete unnecessary 'page headers' throughout as in rows 2-6
I import a text file that displays outstanding parts orders. The order Number column only displays one number even if there are multiple parts ordered I.E. so if three parts are on order then the order number is displayed once with the other two underneath blank. I would like to filter the column that displays the amount of stock that is available so that even if the row has no stock but the other two have all three rows are displayed that reference the order number.
Example Screen
Example.jpg
I was curious if it is possible to give a single cell multiple number formats based on what the number is in the cell. So for example if my number is bigger than 1000, I would like to use comas so that it looks like this 1,000. However, if it is less than 1000 I would like it to look more like this 999.00.
View 6 Replies View RelatedI have checked archives, some similiar but not quite what I want.
In code below I want the output instead of going offset one column to right in same row to go one column to right into the next blank cell.
What I'd like to do is; If column C contains data then insert a blank column and shift column C to the right.
View 4 Replies View RelatedI have a value in A1 and I have a constant formula from Column C to G. what I want to do is if column A has no value then Column C to G should not have values. If column A has values then Column C to G should calculate the values. I have a sample excel attached. I can't figure out what to do! I've tried conditional formating but dosn't seem to work
View 6 Replies View RelatedI have a spreadsheet with three columns (A,B,C). I want the third column C to be column A - B (A minus B) for each row, but only if there is a value in column B.
If there is no value in column B, then I want that row in Column C to just stay blank.
I have a file where I can not enter the VBAProject. If I click on it I get an error message and when I try to get into the Properties of the Project I get the same Message. The error message is "Project locked" (in german). I locked the file my self some month age, all passwords are saved but i am not asked to enter one. I think that i can remeber I was writing a macro in a nother file that locked and unlocked this specific file.
View 3 Replies View RelatedI have a shared file that is now mysteriously locked. How can I unlock the file so changes can be made?
View 4 Replies View RelatedHere's what I have.. (on a much smaller scale)
http://www.jmetenterprises.com/produ...pics/excel.jpg
(notice how the lines that match are now even.)
[Edited by admin~ *Link* to large images, don't display them]
I'd like a macro that does 3 things..
1. Find the last row (cell) of data in the "Customer Number" column. This search should be by the name "Customer Number" rather than by column letter because the column that "Customer Number" will be in can change.
2. Find the column named "Purple" (also by name for same reason)
3. If the "Purple" column has no blank cells in those same number of rows as the "Customer Number" column, delete the whole "Purple" column.
I have a workbook that usually looks something like this
Category Product No description Price
Balloons 12345 Red Disney balloon .50
Balloons 12567 Blue Princess balloon .86
Balloons 76521 Angry Birds Balloon .80
Kites 23456 A Big red Kite .27
Kites 22222 A small blue kite .06
Banners 10000 Party banner .33
etc..
I need to find a way to copy the category below an empty row from column A and paste it in the blank row in column B . If possible to bolden the text but I could probably work that bit out myself. I'm new to this but have used VBA before to run macros.
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.
A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X
After going through some posts in this site, I found that, we can use custom validation for locking a cells when the condition is not met.I have tried using that, But I am not able to get it correctly.I have attached the sheet for your ref.
What I want is, when I enter a month in A2 (Which will always be the 1st of that month), that particular column should get unlocked/should be able to edit that month's column only. I have just provided on table here, but what I want is for entire column so that, I can use the same formula for a larger sheet
code that will unlock cell g12 and af9 when cell e9 equals "WF-"
Basically I need it to say (I really don't know how to write code)
If "E9"="WF-" unlock "G12" and "AF12"
If not, clear contents of "G12" and "AF12"