Results 1 to 7 of 7

Thread: Insert new row on top in a table when a certain cell is populated

  1. #1
    Neophyte ProbioT's Avatar
    Join Date
    Jan 2019
    Posts
    4
    Articles
    0
    Excel Version
    2007

    Insert new row on top in a table when a certain cell is populated



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello everyone,
    Can someone help me with this, please:

    I have a table with columns A, B, C, D, E. The table header is in the first row of the Sheet (A1: E1).
    I'd like to know VBA code to insert a new blank row on the top in the table each time when the cell E2 is populated.
    Thanks a bunch.

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    727
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Try the Worksheet_Change event in the sheet module
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Address = "$E$2" And Target <> "" Then
            Application.EnableEvents = False
            ActiveSheet.ListObjects(1).ListRows.Add (1)
            Application.EnableEvents = True
            'position cursor
            ActiveSheet.ListObjects(1).DataBodyRange(2, 1).Select
        End If
    End Sub
    some info about tables:
    https://www.thespreadsheetguru.com/b...t-excel-tables

  3. #3
    Neophyte ProbioT's Avatar
    Join Date
    Jan 2019
    Posts
    4
    Articles
    0
    Excel Version
    2007

    RE: Insert new row on top in a table when a certain cell is populated

    @Nos: Thanks for your suggestion. It did not work for some reasons.
    I Run Sub (F5) and a window pops up where I need to select a macro.
    I did not have any recorded macros to do it. So, I created this one:

    Sub AddTopRow()

    ' AddTopRow Macro

    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2").Select
    Selection.ClearContents
    End Sub

    This still wont't work as expected (when "E2" populated a new top row needs to be inserted).
    Any other suggestion?
    My apologies, I an a newbie in Excel with a lot of passion for it.
    Thanks again.

    Quote Originally Posted by NoS View Post
    Try the Worksheet_Change event in the sheet module
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Count > 1 Then Exit Sub
        If Target.Address = "$E$2" And Target <> "" Then
            Application.EnableEvents = False
            ActiveSheet.ListObjects(1).ListRows.Add (1)
            Application.EnableEvents = True
            'position cursor
            ActiveSheet.ListObjects(1).DataBodyRange(2, 1).Select
        End If
    End Sub
    some info about tables:
    https://www.thespreadsheetguru.com/b...t-excel-tables

  4. #4
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,572
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Hi,
    When posting code, please wrap it with code tags ( Edit code - select code - click the #button.)
    It keeps the macro's structure and makes it easy to copy and handle.
    Thank you
    Thank you Ken for this secure forum.

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    727
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Here's the exact macro I suggested in a sample Workbook.
    ProbioT_sample.xlsm
    Event code belongs in the Worksheet Module.

    This page may be of assistance
    https://www.excelcampus.com/vba/code...nt-procedures/

  6. #6
    Neophyte ProbioT's Avatar
    Join Date
    Jan 2019
    Posts
    4
    Articles
    0
    Excel Version
    2007
    You are awesome NoS. Thank you, thank you so much.
    You made my day.

  7. #7
    Neophyte ProbioT's Avatar
    Join Date
    Jan 2019
    Posts
    4
    Articles
    0
    Excel Version
    2007
    @Pecoflyer: My sincere apologies for it. I miss it. Sorry.
    Last edited by ProbioT; 2019-01-16 at 05:33 AM.

Tags for this Thread

Posting Permissions

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