New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Append Row Inheriting Formats & Formulas

Free Excel Macros

Append Row Inheriting Formats & Formulas

Postby Sisyphus » Sat Aug 25, 2012 11:35 am

This macro appends a blank row to the bottom of a worksheet. The new row inherits all formats and formulas, but none of the data, from the row above it. It works equally well with all Excel versions since 2003 (and probably older, too).
To install this procedure in your own project download the attached workbook, open it in the same instance of Excel as your own project and drag the module ‘AddRows’ into your project. You can also export/import the module or copy and paste the code to any existing standard code module in your project.
The code copies the last existing row and pastes it into the next blank row at the bottom of your worksheet. This ensures that all formats and formulas are preserved. Then data are deleted, but not the formulas. So, new data can be entered. The code also adds the current date and selects the first cell where data should be entered. These functions can be deleted and/or more actions to be taken on the new row added, all at the location marked in the code.
At the top of the code module is an enum. If you copy the code to an existing module you must make sure to move it to the top of that module because enums must be declared before any procedures. Read more about enums on the forum ‘Tips and Tutorials’ on this site. The enum here has four values that you can adjust to the values your project requires. They define the first row of your worksheet containing data and the column for testing for last used row. These two are required. The other two define the columns for date and selection. If you remove these functions you can also ignore enum values. Else you can specify in which column to write the date and which column to select, ready for the user to make the next entry.
This post includes two methods for calling the procedure. One is the procedure ‘AddRow’ which can be called from the macro button or from a keyboard shortcut or button you might set up for this purpose. Try it by placing the cursor in the procedure in the VBE window and pressing F5.
The other method allows you to simply double-click on the first cell in the first blank row at the bottom of your worksheet. To install this feature you need to copy the following code to the code module(s) of the worksheet(s) in your project where you want the feature to be available.
Code: Select all
Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim R As Long

    R = LastRow(NwsTestForLastRow) + 1
    If Target.Address = Cells(R, 1).Address Then
        Cancel = AddRows.AppendRow(ActiveSheet, R)
    End If
End Sub
You still need to install the module AddRows. However, if you pasted the code to another module you should change the module’s name in the above procedure to what it is in your project. You may also remove it altogether since the purpose of its mention is only to show the connection to the human reader, not a requirement of VBA. The code is also included in the attached workbook so that you can test the double-click action there.
You do not have the required permissions to view the files attached to this post.
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