Prevent Insert New Rows Or Columns

May 21, 2009

What is the easiest way to stop users inserting new columns or rows into a workbook? Is it a case of protecting each sheet individually?

View 6 Replies


Prevent Selection On Hidden Rows/Columns

Sep 21, 2007

One of the fields I want them to enter is a library branch and I have used a data vaslidation list to provide a dropdown box.

To make it act more like a windows drop down box I have hidden 7 cells above this box with the 7 possible entries. This means that with autocomplete, if they start typing in the cell it will autocomplete the entry for them.

This work fine, but if someone presses enter from the cell above my hidden rows, one of the hiddenn cells is selected!

I have no explanation for this behaviour, nothing like it has ever happened before, and I have no idea how to get rid of it.

example attached

View 9 Replies View Related

Prevent User Seeing Senstive Information In Hidden Rows/Columns

Aug 19, 2008

I have a feeling the answer to this will be 'no' based on the searching I've done, but is it possible to allow formatting of rows/columns on a protected worksheet, yet prevent the user from unhiding some rows/columns I don't want them to see? I've allowed them to format rows/columns so they can "size" them to fit the text they enter, but that has opened up the ability for them to unhide columns. Using Shrink To Fit would be cumbersome due to the number of cells involved.

View 5 Replies View Related

Columns To Insert The 2 Rows

Aug 22, 2007

I have this excel file which has data in it. However, this data will come in everyday. Eg, A1 to A10 is QWE, A11 to A20 is RTY, A21 to 30 is UIO. But as I said earlier new data will come in everyday. For eg, it will become A1 to A15 is QWE, A16 to A30 is RTY and so and so forth.

I need to insert 2 rows after QWE, RTY, UIO. But as data will come in everyday, I cant standardise my columns to insert the 2 rows.

View 14 Replies View Related

Compare Columns And Insert Rows?

Aug 16, 2012

I have attached an example of what I need the macro for. I really need it to look at the columns with the names (Girth Weld, Weld) and the Joint length column to make its moves. Mainly the joint length. I think that would get me on track and give me some room to explore and learn what else I need to do. I love to try and figure things out but this is killing me!


Sub LineUp() 'assumes data in order and concates unique
Dim i1 As Long, i2 As Long, n1 As Long, n2 As Long
Const StartRow As Long = 5


View 4 Replies View Related

Unable To Insert Columns Or Rows

Apr 7, 2013

I 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 Related

Insert Two Columns For Selected Rows

Dec 6, 2008

I would like to select a number of columns and then run a macro that would insert two columns after each of the selected columns.

Or is there perhaps a faster way than using a macro?

View 9 Replies View Related

F4 Does Not Repeat Insert Rows Or Columns

Sep 12, 2009

My F4 key (which I use constantly) works for every "repeat" function except for inserting rows or columns. I can repeat every other option EXCEPT inserting rows/columns. I just had Office 2007 uninstalled and had Office 2003 put back on my PC. My IT group can't figure it out.

View 3 Replies View Related

Insert One Or Two Rows Based On Two Columns Criteria

Feb 3, 2009

I need to insert one or two rows depending on the criteria of two different columns.
We have two shops (A and B)...and the sales are expresed like this: ....

View 9 Replies View Related

Insert Rows Based On Number Of Columns Used

Jan 31, 2008

I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.

Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)

View 8 Replies View Related

Compare Columns & Insert / Delete Rows

Feb 22, 2008

I have a large data file. In column A is 8760 entries (every hour of every day of the year). Columns D & E are linked, Column D is the time and date whilst Column E is a parameter assocaied with that time and date.

There should also be 8760 entries in these columns, however there are some missing data lines in columns D & E. I would therefore like to move the rows in columns D & E so they match with the entries in column A. Can anyone help? I have a headache as i have over 70 files to complete this operation on.

View 9 Replies View Related

Count Columns With Data Then Insert That Number Of Rows

Feb 11, 2014


01012014 DAVE JOHN
02012014 MAT

What I want

01012014 DAVE
01012014 JOHN
02012014 MAT
03012014 CHRIS
03012014 MIKE
03012014 PHIL

View 4 Replies View Related

Excel 2007 :: Disable Insert Columns / Rows Only

Mar 1, 2012

I am looking for a way of allowing users to edit the existing cells at their will, but simultaneously disabling the option to insert/ delete columns or rows.

By enabling Sheet Protection, I cannot find a way to do it. Can I?

I am on MS Excel 2007 by the way.

I found the way, just unlock all cells in Format Cells, Protection Tab. Then Protect sheet and de-select what you want to disable.

View 1 Replies View Related

Insert Rows/Columns Without Formula References Changing

Aug 31, 2006

I'm working in a workbook with several sheets, the first two collums of each sheet are =to the first sheet. some times I need to insert a line, but when I did that, I need to type again all formulas or drag the previous ones, and format again the cells. is there another way to do this?

View 4 Replies View Related

VBA Macro To Delete Rows/Columns, Insert Formulas & Auto Fill

May 28, 2009

I'm running a macro that opens another workbook and read data from it.How can I incorporate this code into my macro.Sorry i don't knwo VBA.

Workbooks.Open Filename:="C:Documents and SettingsmsimantbDesktopINFRACHEM_POLYMERS - DON''T DELETE.xls]Sheet1"

UserGRP_MAcro Macro
Selection.Delete Shift:=xlUp
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlToLeft
Selection.Delete Shift:=xlUp
ActiveCell.FormulaR1C1 = "Existing userGroup"............................

View 2 Replies View Related

Delete Columns, Increment Alphanumeric Text, Insert Rows & Sort

Feb 18, 2008

I am having to write a vba code for a survey data captured code online.

I am attaching the code and the vba module that is suupose to do

following things


3) Initial 6 letters from each cell (Q0001=) UPTO (Q00011=)

4)Inserts new row at top from Q1 TO Q11

5) Sorts data on Q6 (Faculty/Staff)

Before all this the i need to insert blank cells for questions that have not been answered and shift the cells right (condition 1) ....

View 9 Replies View Related

Excel 2010 :: Cell Insert In One Column To Insert In Other Columns?

Mar 23, 2012

I have a master data sheet with four columns, A, B, C and D

Column A has the primary data and B,C,D has dependent data values;

So when I insert a new cell in Column A with cells Shift Down, I want mandatorily new cells to be inserted in the same row in col B, C and D as well so that data integrity is maintained;

View 2 Replies View Related

Prevent #N/A! In Certian Columns

Jul 15, 2006

I am trying to create an invoice but the invoice show #N/A in certian columns when there is no number which already have formulas, how do I modify them using the if function?

View 9 Replies View Related

Prevent Labels From Spilling Into Other Columns?

Jan 1, 2014

When you put a label in a column, that is wider than the column, and you don't wrap it or shrink to fit, etc. it "spills" into adjacent columns so the whole label is displayed. In some cases this might be desirable, but in others not.

You can prevent it from spilling by putting a blank space into the next adjacent column -- that truncates the display of the label -- but clearly this is not a good solution.

Is there a way to prevent labels from doing this so that only the portion of the label that is in the cell where the label is entered is visible on the spreadsheet? Of course you can see the entire label by selecting the cell itself, and that's what I want to do without it spilling into other columns.

View 4 Replies View Related

Prevent Hidden Columns Printing

Mar 21, 2008

I have a sheet that uses Blocks of cells eg: A1:X10,A12:X22 etc to hold data/text etc.

I am filling colums A to V with data and Ranking and summing them in W & X,
I don't need to show K, L M ...for example, until they are totally filled, so I hide them manually until needed.

Have a Macro to print "results" A1:X10,A12:X22 etc, and I set a "print area" or "print selected" fine to fit to 1x1 Page.

Problem: If I hide L, M, N etc, the macro runs fine but still uses the unhidden size of the selection to fit the page ie: I get A-B-C-D...-W-X as required, but the other half of the page is blank, because the size selected is for all including the hidden columns.

Q: How can I (Simply) print what's visable to 1x1 Page, fitting more in each time I unhide a column or two.?

View 6 Replies View Related

Prevent The Users From Inserting Columns Within A Certain Range?

Jan 7, 2010

I am instantiating Excel 2003 from and populating the spreadsheet from SQL. The workbook itself will be maintained even when the .Net program is inactive (so that the column headers are established and saved.) Is it possible to prevent the users from inserting columns within a certain range?

View 2 Replies View Related

Prevent Formula #REF! When VBA Macro Deletes Columns

Aug 5, 2009

I'm using the following code to delete columns:

Dim rng As Range
Dim i As Integer, counter As Integer

Set rng = Range("1:1")

i = 1

For counter = 1 To rng.Columns.Count

'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
i = i + 1
End If


My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?

View 4 Replies View Related

Prevent Certain Rows Printing

Jan 9, 2007

By adapting a worksheet macro obtained previously via Ozgrid, I built a workbook that dynamically produces a database, in a worksheet titled “NumLtrWBS DB”, based on all possible combinations of what is listed in column A of three 3 worksheets titled: “ Num”, “Ltr” and “WBS, respectively.

The macro, named “NumLtrWBS", works wonderfully, until I add a row at the top of each list to contain a column header. The unchanged macro includes the column headers in the “produced” database. When I change the macro by altering the variable definitions to begin at A2 rather than A1, it still includes the column headers in the “produced” database. How should I change the macro so it doesn’t “produce” the column headers in the database worksheet?

View 3 Replies View Related

Prevent Hidden Columns From Showing Up When I Save It As A Txt File.

Mar 20, 2009

When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.

View 2 Replies View Related

Prevent Empty Rows From Printing

Nov 10, 2005

I have formulae in cells to reference another sheet, but if the referenced cell is blank, then the new cell I made blank (using the IF function). The problem is, when I want to print, Excel wants to print all of the cells with entries in them - even the ones with formulae in them that are blank.

Is there any way to prevent the empty cells from printing?

View 11 Replies View Related

Prevent #REF! Error When Deleting Rows

Mar 18, 2008

I have a form on a SheetA wherein all cells are referencing the data in cells on SheetB. When I delete or add a row on SheetB, the cells in that row on SheetA read REF#. I need the row in SheetA to be added when I add it in SheetB and vice versa for when I delete a row.

View 5 Replies View Related

Prevent Excel From Adjusting Formulas When Inserting New Columns Of Raw Data

Nov 12, 2013

I have a sheet with 2 tabs. On the first tab is my data and the 2nd tab is the formula.

This is my formula;


When I insert a new column at 'A' the formula changes (as below)


How do i stop the reference changing from 'Data!$A:$AD' to 'Data!$B:$AE' when inserting the column?

View 2 Replies View Related

Deleting Rows That Have Dependent Cells. Prevent !#REF Value

Oct 27, 2007

Deleting rows that have dependent cells.

I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet “=VALUE(Sheet1!B5)”.

My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in
sheet 2 “=VALUE (Sheet1!#REF!)” Is there a work round this, to prevent the formula showing a #REF! Value.

Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.

View 10 Replies View Related

Prevent Text Being Split Down Rows On Import

Nov 22, 2007

I have a large sheet that I am having some problems with. The information comes from our database. In column A I have a client code and other information over to column D. In columns E I have dates. These dates have comments that are associated with them entered in column F. The problem is that sometimes these comments wrap into the next cell below. I have done this in the past. But the problem is that in column G there are dates of when the action took place, the issue is that there might be 1 or 10 of these dates per client. And then in Column H there are comments as well that spill into the next cell below.

What I need is to have everything in the first line, the client code over to column E, Combining of the cells in column F that were broken over multiple cells, then in Column G the first date and its combined actions to the right. The next row might have no client code but might have another date in Column G and comments in column H.

This is really hard to explain what I am looking for exactly. I have attached a small file that has an example of what might be downloaded and then a sheet of what I need it to look like when it is done.

View 8 Replies View Related

Prevent Formula References Changing When Add/Delete Rows

Jul 11, 2006

On my excell I have a lot of data on sheet 2 that i automatically pull form a database.

I have this fomula in a cell on sheet 1.

= SUMPRODUCT(--( 'Sheet2'!P5:P50000=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

The problem is that when the data on sheet 2 gets updated and more lines added, the computer changes my formula for the cell on sheet 1 as well. the new formula will change to

=SUMPRODUCT(--( 'Sheet2'!P5:P50126=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

when there is 126 lines of new data addes in on Sheet2. Id only 50 lines were added in on sheet 2 then the Formula would change to

=SUMPRODUCT(--( 'Sheet2'!P5:P50050=1),--('Sheet2'!T5:T50000>TIMEVALUE("01:00")))

View 9 Replies View Related

Copyrights 2005-15, All rights reserved