Free Microsoft Excel 2013 Quick Reference

Automatically insert rows after data are entered into last cell

Good Afternoon/Morning/Evening,
I am attempting to create an excel form, B21 through N24 are cells that have information being entered into them, however once b21-n24 are full i need to add a new row and colums with the same formating as b21-n24, i would like the document to either automattically enter the rows and colums once b24 has information entered into it, or place a button or something to the side that adds the next row. there is information however in b25 and so on, so it would have to insert a row inbetween 24 and 25. if anyone has any idea or could help me out with some pointers i know just about nothing about VB code. THANKS in advance

Post your answer or comment

comments powered by Disqus
Is there a way to automatically protect a cell after data is imputed into the cell?

Hi All,

I have a spreadsheet created in Excel 2003 that has many sheets and lots of conditional formatting.

When that spreadsheet is opened, whether or not it is Excel 2007 converted to the 2007 file structure or used in compatibility mode as a 2003 format spreadsheet, Excel 2007 does not let me see data being entered into any cell as I type. After I select Enter the data in the cell appears. I tried removing all conditional formatting and then replacing the formatting (copy, paste special, formats) with no effect.

Can any one help please.


I am very green to VBA, so please excuse my ignorance. I have been searching the internet and forums for days now looking for and trying out macros and scripts that will automatically unhide rows once data has been entered into a cell. The data entered is a link to a cell in another sheet. Everything I have tried is close to what I am looking for but it does not quite work, and because I really don't understand the programming details, I don't know what to change in the script to make it work for my spreadsheet.

If anyone can help me create a macro/script to do the following:

Various rows in my summary sheet will be hidden as they will probably not be used, but in the off chance that data will be displayed in them, I need them to automatically unhide. The data that might be displayed in them is a link from monthly record sheets i.e. "=Jan!F4" or "=Feb!F32"

And because I am taking data from a 12 month period, I need the rows to unhide if any data is displayed between cell range of F5:AC1705. It is a large spreadsheet of data and it is all linked to 12 other sheets.

Unfortunately, I am unable to attach the file as it is too large.

Please if anyone can help?



next cell after enter is hit

I am very computer illiterate and have searched and experimented for 2 days with limited success

here is currently what the macro does

when data is entered into a cell that cell is filled with the color green....simple...and works

also there is 3 different columns with data being entered...A....B....C....after data is entered in C it automatically puts the current date in the corresponding cell in column D....that is somewhat ok...would like it to time stamp it also

here is features that I would like to add

Once data is entered to a cell I would like to lock that data so it cannot be changed....also after data is entered into a cell on column C as I said a date is also posted in corresponding cell in column D...I would like the date/timestamp to be locked also

also to input data we will be using a bar code scanner that is programmed to hit enter after data is for example....when we scan data for column a row 2 and enter is hit(through the Bar Scanner) it goes down to row 3...instead I would like it to go to column B and same row....then after that goto column C same row...then after that go to column a but down a row

I attached the simple spreadsheet

any help would be greatly appreciated

Hi everyone,

Sorry in advance for my very lengthy explanation and question! This is my first post and I hope this isn't a duplicate - I searched these boards and the most recent similar question to mine was in 2004. After 2 1/2 days of searching the internet and reading various forums, I can't seem to come up with the right macro or vba code to have a worksheet automatically protected after a user enters data in a specific column. Let me say up front, I have pretty much no experience with macros or vba code, but my basic knowledge of Excel is good. I've created a spreadsheet that I basically want to use as a time clock. I found vba code that places a static date/time stamp in Column C when a name is entered in Column B. This works great. What I am stuck on is the protecting part. I would like the entire worksheet to be protected automatically after data is entered in Column B and for this to be transparent to the user. I've already got the cells in Column C locked, but that does no good until the entire worksheet is protected, and I don't want the users to do the protecting. Without the worksheet being protected, they can delete the time stamp. I also need the worksheet to automatically unprotect upon opening. Any suggestions would be SOOO appreciated! Here is the code I already have in place for the time stamp:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myIntersect As Range
Dim myCell As Range
Set myRngToCheck = Me.Range("C:C")
With Target
Set myIntersect = Intersect(myRngToCheck, .Cells)
If myIntersect Is Nothing Then
Exit Sub 'nothing to do
End If
Application.EnableEvents = False
On Error Resume Next
For Each myCell In myIntersect.Cells
With myCell.Offset(0, 1)
.NumberFormat = "mmm dd, yyyy hh:mm:ss"
.Value = Now
End With
Next myCell
On Error GoTo 0
Application.EnableEvents = True
End With
End Sub
I hope I've supplied all of the relevant details - if not please let me know.

Thank you in advance!

I am trying to create an invoice sheet for my computer shop. I have never used excel really unless it was created for me.

My issue is that I want to lock a couple of cells after data is entered for example cells B13, C13, and H13 on sheet2 and so on.

I tried creating a macro

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myRange As Range
On Error Resume Next
Set myRange = Intersect(Range("C13,B13,H13"), Target)
If Not myRange Is Nothing Then
Target.Cells.Locked = True
End If

End Sub

But it then locks all after the first one is entered. How can I set them to lock after only they are filled?

I wish to be able to automatically hide a row when data is entered into a
cell in that row.
Any ideas?



I need a macro that will find the last row containing data (i.e., the last cell in column A containing data) on the sheet called "RawData", then enter that particular row number in cell A1 on the sheet named "RD2."

Thanks for your help!


Hello Everyone,
I have a formula in cell A4 that multiplies cell A1,A2 & A3 together but if data is entered into the cell I lose the formula. Is there any way to get around this.


Hi - i'm hoping someone can help me here.
Does anyone know if it is possible that after data is entered into a column on a row and the enter key is pressed, then a new row is inserted above, some formulas are copied from the previous row and a new consecutive record no. is given.
Im sorry if this sounds confusing, it sounds right in my head but here is an example of what i mean:

Agency : Ref No. : Subject : LIM : Date Rec'd : Date for follow-up
LG : 1 : Text : List : Date : Date + 5(days)

After inserting date in col 5 and pressing enter i want a new row to be inserted above, LG to be inserted in col 1, col 2 to take the next consecutive number; i.e. in this case 2, the list in col 4 to be inserted and the formula in col 6 to be copied and the same thing to happen again when i press enter in col 5 again.
You can take it from this request that i dont know much about VBA (or if this can even be done!) but any assistance or suggestions given would be much appreciated.

I have rows (where I enter data) with a running balance formula (in the last column)
and a row (Total row) at the bottom with totals of the columns. ( Total Row does not have data in column A)
I would like it to automatically insert a row after the last row that has a blank in column A. (which would be above my Total Row)
I found this code from jetted that works great, but will not insert the row with formulas.
Sub test()
'find last entree in column A
RowCount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("A" & (RowCount + 1)).Select
End Sub
Any suggestions would be greatly appreciated. Thank you.

Hi, all.

This forum has saved me before...but this next request may be unrealistic.

I have a 4-column worksheet -- an index to a 19th century guardians' docket.

Column A is a name (ex. Linn, Noah C)
Column B is a descroption (ex. guardian)
Column C is a volume number (ex. 1)
Column D is a page number -- or a series of numbers (ex. 2, 4, 5)

Column D is the problem when there is more than one page number. Ideally, there should be a unique record for every page number.

Thus, the entry:

Linn, Noah C guardian 1 158, 187

...should ideally be:

Linn, Noah C guardian 1 158
Linn, Noah C guardian 1 187 that I can save this file as a .cvs document and easily import it into my database program that will let me retrieve each record and display an image of the real long as a single number occupies that field.

This, I have about 800 lines of a worksheet that look like this:

French, Alfred heir 1 16, 18
French, Susan heir 1 16, 18
French, Francis guard 1 16, 18, 40

etc.... Some records have as many as four or five different page numbers.

I have already found out how painful it is to have to insert new rows and then copy/paste relevant info, edit the page field etc.

Is there any part of this process that begs for an automated solution? Even the automatic insertion of rows after each record with multiple entries in the page field would be a timesaver, and I suppose I can manage that by manually pointing and assigning a macro to insert rows and copy the preceding record. But I always dream of something better, something more solve my problem in one fell swoop.

Wanting to avoid carpal tunnel...

Thanks for your consideration.

Hello Bos, can u help me please ??
When I want entry the data like name, address and etc in sheet1 and then I click a button at sheet1, so in sheet2 aotumatic insert the data (copy) from sheet1 and then in sheet2 automatic insert row after get the data from sheet1 (
How can do that ??
Sorry my english grammar is not so good..

Dear Members

I have a large spreadsheet of data and i want to insert a row after each new codes..since there are cells with same codes i need to insert a row after the group. It is a bit similar to subtotals but without a total but a blank row.

If there is a way of doing that please do share the knowledge.

Kind Regards

I have a similar post to the one I am about to post here. I have tried to edit my other post but I don't get the option to do so. The other post's topic is not accurate anymore, and I'm sure very misleading to my current problem. Here is a link to the other post. Please feel free to delete that post, or link it to this one. I'm apoligize for the inconveniance. (if I should go about this another way, please correct me now, so that I don't make any further wrong posts.) Thanks very much.

On to the question,
Is there a way to have a formula inserted into a cell based on information being entered into another cell?

I would like this workbook to have the cabability to:
When data is entered into a cell in column A, insert a formula into column E in the same row as the original data entry.

Please see attached workbook for clarity. You will see that columns D, and E contain formulas in the rows that currently have data. I would like this form to be user friendly when adding new items.

Thanks for the help

How do I get data automatically entered into a cell when I enter data into an
adjoining cell? I'm doing a payroll project, and I basically want to enter a
name in one cell and have that person's hourly wage data automatically
entered in the next cell without having to type in the dollar amount every

Is it possible to have Excel automatically hide a row of data after data is entered in a specific cell? I have a spreadsheet that I am using as an equipment log. There is a field to designate if the issue is open closed. If 'Y' is entered, I want the row to be hidden. I have tried using the autofilter, but it does not automatically filter if I add new data. For example, if the column contains all 'N', and I change one to a 'Y', that row still appears. I then have to select the filter criteria again, and for some reason it is not hiding all the values that correspond to the criteria. Is it even possible to have Excel automatically hide a row of data once data is entered in a specific cell in that row?

Thanks in advance.

Hi everyone,

First post in here - first of all would like to thank everyone for the help this site has already given me...i've picked up so much from just browsing through the forums already!

I've had a look to see if this has been asked anywhere but couldn't find an answer to this particular here goes:

For reference:
- Code is being entered in the worksheet_change event area
- general purpose of code is to make changes to a number of specific cells in the same row when data is entered into a cell in a specific column
- the specific problem I am having is with activating a particular instance of Data Validation

Summary of Problem:
The code looks to see if data is entered into any cell in Column G. When such a change occurs, a number of other cells then have various changes made to them. This particular issue relates to two of these cells - Columns E & F
The code for the cell in Column E activates List-type data validation using a dynamic range. This works fine
The code for the cell in column F activates List-type data validation using a dynamic range which is dependent upon the value in Column E. I have set this up using the INDIRECT function, and have used a work around inspired by another thread on this forum to bypass the whole INDIRECT/Dynamic Range problem. (The workaround involves setting up a string variable which is then used to return the dynamic range to a fixed cell which can then be used with the INDIRECT function).

Hers's the specific issue:
The cell in Column E is blank in it's initial state (i.e. after the data validation has been activated but before a value has been chosen from the list by the user), when this is the case and I manually add the data validation to Column F using the toolbar, I get the "source currently evaluates to an error" message (which is to be expected as the cell which the listlookup refers to is also blank). However, i can click 'Yes' to continue anyway and the drop down box is still just doesn't do anything until a value is chosen for Column E at which point the range updates and I the column F list shows the relevant values. However, when the same process is attempted through the worksheet_change event i don't get this error message with the option to continue...the data validation just doesn't add.

So there are three questions:
1) Am i right in thinking that this is because if the data validation activation process in vba hits an error it just fails, rather than allowing the user to force it through manually?
2) If so, how do i either a) make it acknowledge the error ignore it in vba, or b) get around it another way
3) If it is failing for some other reason, then what could it be and how could I solve the issue.

Can't attach code at the moment - the file is on my work laptop and I'm writing this from home. Have tried to explain the issue as clearly as possible - hope I've done a decent job!

Many thanks in advance for any help...this is really stumping me!

hi All,

i have an excel file containing hundreds of rows and columns. one column is having different dates for the next 10 years.
Daily i have to manually insert 3 rows between every change in date.
Somebody please help in writing a macro to auto insert rows after every change in date (Dates are in column).

Your help will really change my hours and years.

looking forward for your kind help

bye and cheers

Anil kumar

I get get a spreadsheet each day that gives me orders by day for the year by
Offer #.
If there was no order on a particular day for an offer, it skips that day.
I want to insert the missing day in the correct row and auto insert "0" for
number of orders and $. Ex. -in the data below, there were no orders beetween
12/23 and 12/27. I want to automatically insert rows with the missing date
and "0" for orders and Deamand. Is there function or snippet of code to


PX05C1A* PX05C2A*
OrderDate Orders Demand Orders Demand
12/23/04 1 $39.00 0 $0.00
12/27/04 4 $214.50 0 $0.00
12/28/04 2 $68.00 0 $0.00
12/29/04 1 $118.00 0 $0.00
12/30/04 10 $799.50 0 $0.00

I have created a data set that is updated every month with new prices. The
prices are entered into a cell, say A1, then the next 3 cells (B11) to the
right use different formulas with that data to give me information I need. I
was wondering if there was a way, so that when I enter new data in A2 for the
next month, the 3 cells to the right of A2 will automatically apply the
formulas from B11 (into B22). Under Tools>Options>Edit the 'Enable
AutoComplete for Cell Values' and 'Extend Data Rage Formulas and Format' are
checked off. Also, under the Tools>Options>Calculations tab the 'Automatic'
is also checked off.

Any help on this would be great appreciated, as I am great confused!


Automatically Add Date Into Cell It is often asked how one can have a date, or date and time, entered into a corresponding cell after data in entered into other cells. Let's suppose you need the current date entered into Column "B", on the same row as Column "A" data is entered. The date entered must be static so that means we cannot simply use the TODAY(), or NOW() functions.

I get get a spreadsheet each day that gives me orders by day for the year by
Offer #.
If there was no order on a particular day for an offer, it skips that day.
I want to insert the missing day in the correct row and auto insert "0" for
number of orders and $. Ex. -in the data below, there were no orders beetween
12/23 and 12/27. I want to automatically insert rows with the missing date
and "0" for orders and Deamand. Is there function or snippet of code to


PX05C1A* PX05C2A*
OrderDate Orders Demand Orders Demand
12/23/04 1 $39.00 0 $0.00
12/27/04 4 $214.50 0 $0.00
12/28/04 2 $68.00 0 $0.00
12/29/04 1 $118.00 0 $0.00
12/30/04 10 $799.50 0 $0.00

As a pivot table changes when new fields or items are added, how can I automatically insert rows or columns to adjust over or down tables adjoining the pivot table? The tables have formulas linked to the pivot table and both are formatted to look like one table.

No luck finding an answer? You could always try Google.