New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Add A New, Blank Row Copying Format And Formulas From Another Row

Free Excel Macros

Add A New, Blank Row Copying Format And Formulas From Another Row

Postby Sisyphus » Sat Apr 21, 2012 4:30 am

After you set up your worksheet with all its formats and formulas you usually need to add more rows to it that have the same formats and formulas but not the contents already entered. This little gadget will do just that:
Code: Select all
Public Sub AddRow(Optional ByVal Rt As Long, _
                  Optional ByVal Rs As Long, _
                  Optional ByRef Ws As Worksheet)

    If Ws Is Nothing Then Set Ws = ActiveSheet
    If Rt < 1 Then Rt = LastRow(1, Ws) + 1
    If Rs < 1 Then Rs = Rt - IIf(Rt = 1, 0, 1)
   
    With Ws
        .Rows(Rs).Copy
        .Rows(Rt).Insert xlShiftDown
        Application.CutCopyMode = False
        On Error Resume Next
        With .Rows(Rt)
            .Cells.SpecialCells(xlCellTypeConstants).ClearContents
            .Cells(1).Select
        End With
    End With
End Sub
In order to run this procedure you need the function LastRow which you can copy from this site at the following link:
http://www.excelkey.com/forum/viewtopic.php?f=5&t=393
Paste it in the same module as the above procedure, which should be a normal code sheet, called 'Module1' by default. You can change the name.
AddRow's syntax is very simple:
Code: Select all
AddRow [Target_Row,] [Source_Row, [Worksheet]
As you see, all the arguments are optional. You don't need to use them, but if you do the procedure becomes a very powerful tool.

  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. Use this code to do that: Ws.Activate

There are many meaningful ways to call the above procedure. You may like the simplest one of all. Paste this procedure in the code sheet of the worksheet where you want the blank rows inserted:
Code: Select all
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = Cells(LastRow + 1, 1).Address Then
        AddRow
    End If
End Sub
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.

As you see, this procedure also calls the LastRow function already mentioned above. Make sure that it is declared as 'Public' (it is 'Private' when you copy it from this site and need not be 'Public' if you only call it from the other module).
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 24 guests