Adding a row to my worksheet.

Posted on 2012-12-31
Last Modified: 2013-01-06
I want to be able to add a row to my worksheet when the last row has data using VBA.
I also want to copy the formatting that is in the previous row.
Can you help me please.
Question by:LS92118
  • 3
  • 3
  • 3
  • +2
LVL 46

Expert Comment

by:Martin Liss
ID: 38733956
Here's a macro you can use. By 'previous row' I assume you mean the row that was the last row before the new one was added.

Sub AddRow()
    Dim LastRow As Long
    LastRow = ActiveSheet.UsedRange.Rows.Count
    Rows(LastRow & ":" & LastRow).Select
    Rows(LastRow + 1 & ":" & LastRow + 1).Select
    Application.CutCopyMode = False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Open in new window

LVL 14

Expert Comment

ID: 38733995
If you want a more demanding / versatile solution try the one demonstrated in the attached workbook. I developed this for posting on another site (they won't let me link you to that site here) and copy from what I wrote there, more or less:-
At the heart of the action is the Sub AddRow which will copy not only formats but also formulas, but not constant values, from a source row. It allows you to specify the row you wish to insert, the row from which to copy and the sheet on which the action is to take place. All these arguments are optional. If you don't supply them defaults will be used as explained in the following.

1. Ws = Worksheet
If you don't furnish this argument the procedure will work on the active worksheet. Unless you are running a complicated project where you add rows on a worksheet that you don't want to activate just yet the currently active worksheet will suit your requirements just fine and you don't need to specify anything.
2. Rs = Source Row
I often want formats and formulas copied from the first row. The argument is that, if I ever want to introduce changes I just have to introduce them in the first row and all new rows will take them from there. If this is what you want, just supply the number of your first row to the procedure. Remember that arguments must be supplied in the sequence of the parameters. If you want to supply Rs but don't supply Rt you must pass the argument as a named argument, like this AddRow Rs:=3. If you don't want to supply Rs or supply Rs<1 the procedure will copy from the last previous row in the worksheet. I have found that the logic of why to copy from the first row applies to the last row equally well.
3. Rt = Target Row
You may not wish to add the new row at the bottom of your worksheet which is the default. In some projects you prefer to add new rows at the top of the list or at some calculated location in your worksheet. By specifying Rt you can add your new row anywhere you want. If you specify, say, 12 a new row No. 12 will be inserted. The row that used to be No. 12 will become row 13. Unless you specified another source, formats and formulas will be copied from row 11 which will continue to be row 11.
4. On Error Resume Next
The source row is copied with all its contents. Then, in the copy, all contents except formulas is deleted. If there was nothing to delete because all cells were already blank an error occurs. The On Error Resume Next statement ensures that you never notice.
5. .Cells(1).Select
This line selects the first cell in the new row. If you don't want that cell selected, delete the line or change the cell. Cells(1) is in column A, Cells(2) in column B etc. If you have inserted the row in a worksheet other than the active one the cell will not be selected and an error occurs. However, due to the preceding Resume Next statement your program will continue running. If you wish to select a cell on a worksheet which isn't active you should activate the worksheet first. Insert .Activate immediately after the line With Ws.

There are many meaningful ways to call the above procedure. You may like the simplest one of all. The attached workbook has a small even procedure in the code sheet of the worksheet where you want the blank rows inserted (Sheet1). The effect of this procedure is that a new line will be added to your worksheet whenever you click on the first cell in the first blank row at its end. This would be easy to change to act on double-click (BeforeDoubleclick even instead of Selection_Change event).

I have added another method of calling the procedure in the attached workbook. A line will be added if you run the macro AppendRow manually. You can specify the arguments there, if you wish. In fact, you can have both methods of calling the procedure and use different arguments with each method.
LVL 46

Expert Comment

by:Martin Liss
ID: 38733997
I'm sure it works well, but it seems like overkill for such a simple requirement.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 38734019
To Fautulus ,

I was wondering if you would look at this sheet and help me out.
There are different sections.  Auto, fire, life health, bank
And what I want to do is add another row in the category when needed.
So for example on the Auto Section, when an amount is put into cell g16, I would like a new row added to row 17 with the formating from row 16 and the cells clear.
And continue on each section.  
I'm including the worksheet for you to see.
I appreciate any help you can give me.
LVL 14

Expert Comment

ID: 38734028
Hi Linda,
I'll do this for you, but I can't do it now because I'm practically on my way to the airport with a 12-hour flight before me. If you have the time to wait I can get this done within 72 hours. You will definitely need a program like the one I offered because you need the capability to insert at the end of a section, not necessarily the end of the sheet, as you know, of course. However, to find the exact row will definitely need some additional programming.
BTW, do you also want the current date inserted in the new row?

Author Comment

ID: 38734061
Hi, thank you so much for your help.  Yes I can wait, of course,  have a safe trip...
Yes, I would like the current date inserted into the new row, that would be awesome.  
I really appreciate your help.
LVL 46

Expert Comment

by:Martin Liss
ID: 38734117
Can you post the workbook?
LVL 16

Expert Comment

ID: 38735014
Hi Linda have a look at this update to your spreadsheet, inspired by MartinLiss & Faustulus. The code is added directly to your worksheet rather than a separate module, and triggered by the Worksheet_Change event. So when you add anything into column G, it checks if the format of the row below is the same as the current row format, and if not, it adds another row of the same format. Note it is an XLSM file now.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo do_nothing
If Not Intersect(Range("G:G"), Target) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
    If Target.Interior.Color <> Target.Offset(1, 0).Interior.Color Or _
       Target.Borders(xlEdgeRight).LineStyle <> Target.Offset(1, 0).Borders(xlEdgeRight).LineStyle Then
        Rows(Target.Row + 1).Insert Shift:=xlDown
        Rows(Target.Row).Copy Rows(Target.Row).Offset(1, 0)
        Rows(Target.Row + 1).ClearContents
        Rows(Target.Row + 1).Cells(1, 1).Value = Date
    End If
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Open in new window

Let us know if it works for you
LVL 33

Expert Comment

by:Rob Henson
ID: 38736241
There is an option for Formats & Formulas to be extended automatically when data is added to a row immediately below existing data.

Currently working with xl03 where it is in Tools > Options > Edit > Top right option.

For xl07 and later I think it is under Excel Options with the Big Button.

Other option would be to convert the data to a table which will have similar affect.

Rob H
LVL 14

Accepted Solution

Faustulus earned 500 total points
ID: 38739222
I have modified your workbook to give it the capabilities which you requested. Unfortunately, I was nearly done doing my own design before I noticed that you wanted a row to be inserted when a value is entered in column G. Such a plan would either give weird results or require additional safeguards if the value in column G is modified. My design is simpler and I hope it will appeal to you although it wants more clicks. If not, I will modify the code to meet your preference.
If the number of rows in a section is large enough to push the section header row out of the monitor I would prefer the trigger cell to be the last cell in column A. I chose the trigger cell to be in the header row because it takes the least time to identify it.
Anyway, please read the program description in the tab of that name contained in the workbook and let me know what you would like to have improved or modified.

Author Closing Comment

ID: 38749820
Thank you very much for your time!!!

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question