It was suggested that cells formatted as lists will now allow new rows or columns. I don't think this is case but not sure how to tell? Other suggestions were merged cells or cells protected. Not sure if any of these are the case either.
View 5 RepliesI am unable to insert sheet rows or columns in a microsoft office spread sheet in a specific file. Other files are Ok.
View 14 Replies View RelatedI need to make a excel costing model to calculate various products prices. I am using Excel 2010.
I have many products and do not need all of them displaying at the same time, therefor I inserted a Checkbox (ActiveX Control) that hides my columns that I do not need. I did that by inserting the following and it works fine :
[Code].....
The problem is I now need to lock certain cells so that they can remain fixed and the recipe cannot change.
Once locked my checkbox no longer works and I get the following error :
"Run-time error '1004': Unable to set hidden property of the range class"...
i've seen workbooks in the past that have a limited number of rows and colums but im not sure how to set that up in excel.
View 2 Replies View RelatedI have a worksheet that is shared. It has hidden rows and columns. Problem is those hidden rows and columns are unhidden when someone apart from me opens the worksheet! Only way round it is to have the worksheet unshared. How do I keep the rows and columns hidden?
View 3 Replies View RelatedI am unable to insert columns on any worksheets. Even a blank worksheet. The insert column function remains disabled. Work sheets are not protected. What can I do to activate this function?
View 10 Replies View RelatedI recently got a new computer and I am unable to copy a worksheet to a new (or existing) workbook. It doesn't recognize it as an open excel file.
View 2 Replies View RelatedIs there an easy way to collapse rows and columns on all sheets in a workbook.
View 3 Replies View RelatedI could swear I used to be able to cut and paste columns in Excel 2010, but for the past week I haven't been able to. When I click on a column and do a right click, "CUT" is greyed out. I can cut any section, but not a whole column.
View 3 Replies View RelatedI need to copy data from one sheet to another in the same workbook. The data is never the same and can change without notice. Here is what I need to do:
The trigger will be in column A with the cells content being 1 through 85 (never all 85 numbers though, mostly 1 to 10 sheets need to be created). As I said it is not consistent and sometimes new ones are added or some are deleted. For instance, if you find a 1 in column A1 and there is not a sheet named "1" then create it and copy all of the rows that have a 1 in column A. When column A changes to another number say 5, create a new sheet and redo the above until all is copied into separate sheets. My problem is that I don't know how to create a new sheet when the number in column A changes.
I have a workbook with many sheets labelled as mmm-yyyy. The constant columns in all the sheets are C,E,R,T, and U.
Is it possible to have a macro do the following: Add a sheet called Summary at the end of the workbook. From the last sheet of mmm-yyyy, copy columns C, E, and R to the Summary sheet. Copy columns T and U from all the other mmm-yyyy sheets to the Summary sheet. All the cells need to be centered.
I have a workbook with 61 sheets in it.
What I need to do is insert two rows at the top of each sheet. Now I know I can manually do it, or write a macro to, that bit's easy.
However, when I insert the rows, a lot of my formulas try to pick out the wrong range.
So for example, lets say we're looking at sheet 1. When I insert 2 rows at the top this style of formula still works...
=B14/B12 (it automatically becomes =B16/B14)
=SUM(B10:B11), =B12+B14, =B12*0.25, these also update automatically.
Even a reference to ='Sheet 2'!B48*2 still updates automatically. But formula's of this kind do not...
=SUM(Sheet4:Sheet10!B5)
(regardless of inserting 2 rows, this formula does not change to =SUM(Sheet4:Sheet10!B7) )
Using two Combo Boxes, I am trying to create an drop down outline form input sheet.
What it does is if you select the first selection in the combo box 1, then it hides certain rows, and deletes certain cells. If you select the second option in the combo box 1, it shows another combo box (2) which has another list of categories.
When I select the option 2 on the combo box 1, it unhides a bunch of rows, and activates combo box 2. However, when I try to select categories on combo box 2, each time it tries to hide/unhide rows I get this message "Unable to set the hidden property of the range class." I have no idea whats going on.
Here is my ....
I've accidentally created over 1 million rows across several sheets which has sent my file size over 35mb.
I desperately need to delete these and cannot find any way of doing so.
They were created through macros copying columns and pasting them elsewhere, although I'm not sure how they decided that 4-800 rows of data was worth a million rows.
Can anyone offer a solution that doesn't entail making a new sheet and rewriting all the forumula's in it?
I'd prefer to edit links with the source workbook open, however I receive this error when I do "A formula in this worksheet contains one or more invalid references." I am able to edit links with the source workbook closed but this takes far too much time to be satisfied with that as the solution. I have used vlookup formulas and the index-match method and they both take about the same amount of time to update. I've separated each worksheet and used the "name manager" add-in and have searched all links for any type of error. I feel like I'm missing something. Is there a way to fix this "invalid reference" error so that I can edit my links with the source workbook open?
View 1 Replies View RelatedI am having problems with some vba codes when I protect my workbook; 'I get an run-time error 1004 Unable to set visible property of the worksheet class'
The code I am trying is:
Sub Stats1_Return_TextBox1_Click()
ActiveSheet.Unprotect "meme"
Application.Goto Worksheets("BCM Database").Range("L15")
Sheets("Stats 1").Visible = False
ActiveSheet.Protect "meme"
End Sub
How to sort this. One more question is there away of protecting you vba code? stop users being able to view or edit them for instance?
For the following code:
Code:
Sub ShowAdmin()
Sheets("Admin").Visible = True
End Sub
I'm getting the following error message:
Run-time error '1004' Unable to set the Visible property of the Worksheet class
I have command button in Sheet 3 (from where I will be running the macro) and I need a macro which will delete all the blank columns and rows in the data containing in Sheet 1.
View 2 Replies View RelatedI am trying to hide all rows and columns with zero balance in the cells with the use of a macro button.
View 8 Replies View RelatedI have VBA code to set a password on multiple worksheets in a workbook. I new need to force a check in the "Format Columns" and "Format Row" checkboxes. The current VBA code is:
View 4 Replies View RelatedIn my Macro , i add a worksheet and depending on certain conditions i decide whether to populate it or not.
At a later point of time i am checking if the newly created worksheet is empty or not using the following check :
If ActiveWorkbook.Worksheets(ISheet).UsedRange.Rows.count <> 0 Then
Now what i have noticed is that even in cases where the "ISheet" is empty the above condition evaluates to true for the first attempt. I cannot understand why ?
let me know if the query is ambiguous and i will post the code snippet
I have recently tried to implement the password protect from viewing code from this site. It worked brilliant on a basic spreadsheet, but when I tried to implement it on a worksheet with links I keep getting the error:
Run-time error '1004'
Unable to set the hidden property of the Range class
I have a problem with a spreadsheet I am working on, and I'm not sure if its something I've done, or whether its corrupted.
Basically, Column A is Hidden and not Locked in the protection tab of formatting. The other columns are Locked and not Hidden.
I used to be able to use the arrow keys to scroll through Column A, but this now doesn't seem to work. I can select the cells within column A with the mouse and I can scroll with the mouse and the scroll bars, but I can't move between the cells with the arrow keys, enter button or tab button.
Does anyone know what might be causing this, or is the workbook corrupt?
I'm on mac Excel 2010 and i'm trying to delete rows..but for some reason they don't go! My workbook is a basic one, just filled with a bunch of formulas, i can't understand why it wont let me delete it.
When i do try to delete it, nothing comes happens and when i try to delete a large amount it comes up with 'not enough memory, continue without undo deleting rows' but my laptop has 8GB and my other laptop has 16GB so i can't see why a 16GB laptop wouldn't be able to delete it.
I have a sheet protected and filters enabled. I use this code to enable sort and filters:
[Code] ........
Nevetherless, when I try to sort, I receive this message in excel: "The cell or chart you re trying to change is on a protected sheet.
To make changes, click Unprotect Sheet in the Review tab (you might need a password)."
i am uable to use autolfilter when protecting sheet via vbacode even though i have checked the autofilter box to be able to use it whilst protected, why does it work when i manually do protection but does not work via vba?
View 9 Replies View RelatedI need to track only certain tasks id's out of entire task id database. I have 1 column of data on sheet "A" that represents task id's I want to track. On sheet "B" I have multiple columns of data, the first column being the task id's. I need to compare the column on sheet A with the first column on sheet B. If there is a matching task id, I need to copy the entire row of data from sheet "B" on to a new sheet "C". If there is no matching task id on sheet "B", I would like to copy just the task id. Each week I would get a new set of data for sheet "B" (which can vary in length) and I need the ability add or subtract task id's on sheet "A" that need tracking. See the attached xls file for a piece of sample data.
View 4 Replies View RelatedI am unable to unhide some rows on a sheet. Row 1 though 34 the row numbers are blue. After that row 65 and on are black. I can unhide columns but not the rows. By the way row 35 through 64 are missing or hidden. How do I unhide everything?
View 2 Replies View RelatedWe are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
long story short, we ended up with an excel sheet like this: Screen_Shot_2014-05-11_at_4.png
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.)
Screen_Shot_2014-05-11_at_46FDQO.png
I googled for days, i only found Visual basic commands i guess? that only merge same rows. and they were poorly made. beside that it didn't work properly. Method to do it automatically?
EXCEL 2013
We are doing a graduation project on an international airport, consist of scheduling flights on check-in counters automatically.
Excel sheet like this:
Were y-axis are the check-in counters and x-axis is the timeline horizon (cell per 5-minutes)
I will do a VLOOKUP, to change each flight number to it's ID from an other sheet.
But the problem is that i want to AUTO-MERGE all cells with same value, because they represent one flight! and if i shortened the column width i can't see anything.
I want it to be like this: (i've done this manually, and it's VERY time consuming with errors because we have to do it for all days.) Any method to do it automatically?
EXCEL 2013