Solved

Adding a row to my worksheet.

Posted on 2012-12-31
11
143 Views
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.
thanks,
Linda
0
Comment
Question by:LS92118
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 45

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
    Selection.Copy
    Rows(LastRow + 1 & ":" & LastRow + 1).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:Faustulus
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.
130101-AppendRow.xlsm
0
 
LVL 45

Expert Comment

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

Author Comment

by:LS92118
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.
Thanks,
linda
WS-AppProduction.xlsx
0
 
LVL 14

Expert Comment

by:Faustulus
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?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:LS92118
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.
sincerely,
Linda
0
 
LVL 45

Expert Comment

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

Expert Comment

by:terencino
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
Else
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
do_nothing:
End Sub

Open in new window

Let us know if it works for you
...Terry
WS-AppProduction.xlsm
0
 
LVL 31

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.

Thanks
Rob H
0
 
LVL 14

Accepted Solution

by:
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.
130102-Append-Section-Row.xlsm
0
 

Author Closing Comment

by:LS92118
ID: 38749820
Thank you very much for your time!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now