Results 1 to 5 of 5

Thread: Insert row, update total formula to include new row
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2015
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert row, update total formula to include new row

    Hi,

    I've got $ figures in range I14:T20, and each respective column has a subtotal formula
    Code:
    =SUBTOTAL(9,I14:I20), =SUBTOTAL(9,J14:J20), etc
    The user can run a macro to insert a line at the bottom of the data which will then be populated with $ figures. My question is, when the row is inserted, how can I update the subtotal formulas to now include the new row? In this case it would be row 21.

    Thanks for your time.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Insert row, update total formula to include new row

    If you were to insert a row within I14:I20, the formulas would automatically expand to I14:I21 to accommodate the inserted row and keep the original cells.

    Have your formulas include one blank row at the bottom, and have your macro insert a row just above it. Then your formuluas will automatically expand their ranges.
    Last edited by AlphaFrog; Aug 14th, 2019 at 12:21 AM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    Board Regular
    Join Date
    Jun 2015
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert row, update total formula to include new row

    Quote Originally Posted by AlphaFrog View Post
    If you were to insert a row within I14:I20, the formulas would automatically expand to I14:I21 to accommodate the inserted row and keep the original cells.

    Have your formulas include one blank row at the bottom, and have your macro insert a row just above it. Then your formulas will automatically expand their ranges.
    Thanks for the reply AlphaFrog, that was one thing I considered, however, because of user preferences, I want to avoid the extra blank row.

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,087
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Insert row, update total formula to include new row

    Then have the macro insert a row above the last row. The last row will shift down. Copy the last row to the newly inserted row above. Then paste new data in the last row.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  5. #5
    Board Regular
    Join Date
    Jun 2015
    Posts
    142
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert row, update total formula to include new row

    Quote Originally Posted by AlphaFrog View Post
    Then have the macro insert a row above the last row. The last row will shift down. Copy the last row to the newly inserted row above. Then paste new data in the last row.
    So simple, yet so genius!

    Thanks AlphaFrog

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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