Avatar of LS92118
 asked on

Adding a row to my worksheet.

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.
Microsoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon
Martin Liss

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


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.
Martin Liss

I'm sure it works well, but it seems like overkill for such a simple requirement.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

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.

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?

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.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Martin Liss

Can you post the workbook?

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
Rob Henson

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Thank you very much for your time!!!