+ Reply to Thread
Results 1 to 6 of 6

Consistently add new row above

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    317

    Consistently add new row above

    Hi. I would like to have a simple macro that automatically adds a new row above the entry row. Unless there is a better way to have the most current entry on top without having to constantly sort.

    Also I would like to have formulas added to new row. See attached example.

    Thank you.

    Peter
    Attached Files Attached Files
    Last edited by Peterino; 09-16-2019 at 12:33 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Consistently add new row above

    Difficult to comment without seeing your workbook and what you consider to be an entry row.

    That said whenever I need to use a database and add new records in a consistent manner the approach I generally use is to create a data entry row above the main table / database range. Then attach a few lines of code to a button that first inserts a new top row to the database, then copies the data entry row to the new blank row and finally clears the entry row ready for the next record.

    So for instance with the database column labels in say row 5, and a data entry range say A2:H2 and named "NewRecord" and the database range named with the dynamic range name "Data" defined as
    Formula: copy to clipboard
    =OFFSET($A$5,0,0,COUNTA($A:$A),8)


    Sub AddRecord()
        Range("Data").Cells(2, 1).EntireRow.Insert
        Range("NewRecord").Copy
        Range("Data").Cells(2, 1).PasteSpecial (xlPasteValues)
        Range("NewRecord").ClearContents
    
    End Sub
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    317

    Re: Consistently add new row above

    Hi Richard. Just checking to see if you saw my earlier reply. Peter

  4. #4
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    317

    Re: Consistently add new row above

    Hello Richard. I guess I am pretty novice with code stuff. I figured out how to do data entry range and database names, but I couldn't figure out where to apply the formula. I also tried adding the vbs which worked when I clicked View, Macro, Run, but not when I assigned it to a button. Also, I did have a sheet attached to my post. Peter

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,293

    Re: Consistently add new row above

    You could add to a button at the top of your sheet


    Sub addrow()
    Rows(3).Insert Shift:=xlDown
    Cells(3, 9).Formula = "=IF(G3="""","""",F3*G3+H3)"
    Cells(3, 12).Formula = "=IF(J3="""","""",F3*J3-K3)"
    End Sub
    Attached Files Attached Files
    Last edited by nigelog; 09-23-2019 at 11:10 AM.

  6. #6
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    317

    Re: Consistently add new row above

    Hi Nigelog

    That works. Much appreciated!

    Peter

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Vlookup is noy populating consistently
    By gsteel in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-10-2018, 09:09 AM
  2. [SOLVED] IFERROR not (consistently) working
    By John 1978 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 04-26-2017, 03:04 AM
  3. Private is not working consistently
    By rhouston08 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2016, 12:44 PM
  4. [SOLVED] =IF formula not working consistently
    By kazphilips in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-12-2014, 03:19 PM
  5. SetSourceData Does not Set Consistently
    By Mordred in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-01-2011, 10:00 AM
  6. charts not behaving consistently
    By StrawberryFields in forum Excel General
    Replies: 2
    Last Post: 04-21-2011, 11:31 AM
  7. CPU Usage consistently above 75%
    By hparteep in forum Excel General
    Replies: 1
    Last Post: 10-27-2005, 10:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1