Macro To Insert A New Row

    Hi, I need to know how to automaticly create a new row below the last data entry. I have a list of items with relevant data in each row. But below all the data I have statistics of total percentages of certain things. I want to keep that at the bottom of the list. When I add another row for more information I want those statistics to jump down a row so I have more blank rows to add in more data. Is there an easy way to do that?


    Also something kinda related. I know you can freeze rows and columbs at the top and left of the spread sheet, is there any way I can freeze those statistics at the bottom so they are always visable but the data on top of it can scroll through? I dont want to split the page cause that kinda causes more problems.


    Thank you.

    Re: Inserting A New Row With Updating Data


    do you mean a button to do it?


    If so something like:

    Code
    Sub Button2_Click()
    Sheets("Sheet1").Range("A10").Select
    Selection.Insert Shift:=xlDown
End Sub


    where "A10" is the first row of your stats/percentages. This will shift all cells from A10 downwards and insert a row.

    Re: Inserting A New Row With Updating Data


    not exactly a button but maybe if any cell in the row above the statistics has anything but blank cells, then add a row. Also every time the stats gets shifted down, they start at a new row so its only at "A10" for the first time it inserts a new row. So that range has to be dynamic in some way.

    Re: Inserting A New Row With Updating Data


    Quote

    Hi, I need to know how to automaticly create a new row below the last data entry. I have a list of items with relevant data in each row. But below all the data I have statistics of total percentages of certain things. I want to keep that at the bottom of the list. When I add another row for more information I want those statistics to jump down a row so I have more blank rows to add in more data. Is there an easy way to do that?

    Below the last row ARE new rows. If you add a row ABOVE your stats row the row will move down and remain the last row.


    Use a Dynamic Named Range for your Functions.

    Re: Inserting A New Row With Updating Data


    Quote from jamierodgers

    do you mean a button to do it?


    If so something like:

    Code
    Sub Button2_Click()
    Sheets("Sheet1").Range("A10").Select
    Selection.Insert Shift:=xlDown
End Sub


    where "A10" is the first row of your stats/percentages. This will shift all cells from A10 downwards and insert a row.


    What that seems to do is just shift the cells downward from the specified range. For me, it didnt insert a row to make everything go down. It needs to be something like ActiveCell.EntireRow.Insert Shift:=xlDown
    But I need the macro to do it automaticly without a button, just based on a certain condition.

    Re: Macro To Insert A New Row


    In this example I have a named range "stats" which is the first cell in column A where the stats/percentages start.


    put this code behind the SHEET rather than in a module


    Re: Macro To Insert A New Row


    This is probably a dumb question but I need it to apply to all sheets, not just sheet 1. I need to make a template so this can be copied to multiple sheets in the work book. Do I put it in General instead of Sheet 1?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login