Announcement

Collapse
No announcement yet.

Automatically insert multiple rows in Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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.

    Code:
    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, 05:09. Reason: revised code
    AAE
    ----------------------------------------------------

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

    Comment


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

      Comment


      • #4
        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

        Comment


        • #5


          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.

          Comment

          Working...
          X