Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Automatically insert multiple rows in Excel

  1. #1
    Join Date
    24th August 2010
    Posts
    3

    Automatically insert multiple rows in Excel

    I have a budgeting spreadsheet broken down by category (i.e. Equipment, Travel, Contract, etc) and at the end of each category, I have a subtotal of the category and at the bottom, I have a grand total. Example is below

    What I need help on is a way to automatically insert a row each time the user inserts an entry (in the blank) so I can provide a template for the user in which I can automatically aggregate the subtotals in another part of the workbook.

    Example

    Equipment
    -Entry
    -Entry
    BLANK
    Equipment SubTotal

    Travel
    -Entry
    -Entry
    BLANK
    Travel SubTotal

    Contract
    -Entry
    -Entry
    BLANK
    Contract SubTotal

    Total

    I found a macro to automatically insert a row but it only will work for the total (or 1 field). I need the ability to auto-insert multiple rows based on where the data is inserted.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Automatically insert multiple rows in Excel

    Copy this code into the worksheet module. It uses the selection change event to insert a new row based on these conditons:

    1. The target cell is in column-A
    2. The target cell is empty
    3. The cell above the target is numeric

    Adjust the column reference as needed.

    VB:
    Option Explicit 
     
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
         
        If Target.Cells.Count > 1 Then Exit Sub 
         
        If Not Intersect(Target, Range("A1:B" & Cells(Rows.Count, "A").End(xlUp).Row)) Is Nothing Then 
            If Target = vbNullString And Target.Offset(-1, 0) <> vbNullString Then 
                Target.Rows.EntireRow.Insert 
            End If 
        End If 
         
    End Sub 
    
    
    Last edited by AAE; September 3rd, 2010 at 06:09. Reason: revised code
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    24th August 2010
    Posts
    3

    Re: Automatically insert multiple rows in Excel

    This works perfectly but only the column defined in the range moves and an actual row is not entered.

    For example, if I have a worksheet like this:

    Item1 $100
    BLANK BLANK
    Total $100

    If I enter an item in the Blank row, it will just push down the total amount and NOT the word total

    Item1 $100
    Item2 $100

    Total
    $200

    I would like it to insert a row and keep the word Total matched with the actual amount, i.e.

    Item1 $100
    Item2 $100
    BLANK BLANK
    Total $200

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Automatically insert multiple rows in Excel

    See revised code in my previous post.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    24th August 2010
    Posts
    3

    Re: Automatically insert multiple rows in Excel

    This inserts the intended row but it inserts the row when entering the data and tabbing from one cell to another. If I enter an item in A2 and press tab, my cursor will go to B2 but my item will get pushed to A3.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Insert Rows On Multiple Sheets
    By huds0047 in forum EXCEL HELP
    Replies: 11
    Last Post: July 15th, 2012, 03:20
  2. Replies: 2
    Last Post: March 13th, 2008, 18:06
  3. Replies: 1
    Last Post: January 22nd, 2005, 06:21
  4. Insert Multiple Rows
    By rbrewer in forum EXCEL HELP
    Replies: 3
    Last Post: June 3rd, 2004, 01:27

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
  •  
porno