Insert and Delete Rows with formula and formating in a table using VBA

  • Hi all,


    I have typical table set up in excel in the layout shown below.



    [TABLE="width: 500"]

    [tr]


    [td]

    Number of extra rows in UK

    [/td]


    [td]

    2

    [/td]


    [/tr]


    [tr]


    [td]

    Number of extra rows in USA

    [/td]


    [td]

    6

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    [TABLE="class: outer_border, width: 500"]

    [tr]


    [td]

    World

    [/td]


    [td]

    State/Place

    [/td]


    [td]

    Month

    [/td]


    [td]

    Year

    [/td]


    [td]

    Total

    [/td]


    [/tr]


    [tr]


    [td]

    UK

    [/td]


    [td]

    London

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Input Cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Manchester

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Input Cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Birmingham

    [/td]


    [td]

    Input Cell

    [/td]


    [td]

    Input Cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [tr]


    [td]

    USA

    [/td]


    [td]

    New York

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Iowa

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td]

    Florida

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Input cell

    [/td]


    [td]

    Formula

    [/td]


    [/tr]


    [/TABLE]


    This is what I would like the users of this table to do.


    1.Insert multiple rows in UK with formulas and formatting carried through (determined by the number they input in the top table)
    2. Delete rows from UK that are not needed (if they delete the number they added in the top table)
    3. Insert Multiple rows in USA with formulas and formatting carried through (determined by the number they input in the top table)
    4. Delete rows from USA that are not needed (if they delet the number they added in the top table)


    Problems I've encountered when using VBA,


    a. On inserting multiple rows in UK i can't get the loop to stop so any click i do on the page inserts more rows
    b. I want the new rows to be added to the last row populated in UK but i lose the title of USA
    c. As i have cell/row references in my code when i add new rows in UK it moves the whole table down and so my cell reference for adding new rows in USA is actually no longer in USA so when i go to add multiple rows in USA they too are added in UK.


    sample of code I've used.




    Is it possible to keep this format for the table and add and delete rows as mentioned above? The reason i want to keep this format is so users an easily copy and paste data from external workbooks as they will have similar layouts.


    *******Eurika Moment*****


    ***************************Can we create a table from scratch with the correct number of rows (which are the changing factors) and columns that will remain the same so matter what is added in the rows.. if that makes sense?!


    so leave the top table as it is where the user will input the number of rows per world column. Then VBA creates a table with the column headings as shown in the second table with formulas and formatting in tact? so if the user needs 5 rows for UK and 6 rows for USA then VBA created a table as such and labels the first 5 rows as UK and the next 6 rows as USA. Is that possible?***********************


    Thanks in advance. Please let me know if you need more information.

  • Re: Insert and Delete Rows with formula and formating in a table using VBA


    I am wondering why you are using VBA at all.


    Select your data and press Crtl-T. This will create an excel table.
    One of the proprieties of an excel table is that it will allow you to insert a row, and the formatting and formulas are inheritied on the new row.
    Table heading integrity for the table is maintained too.


    Give it a go, it looks like it will fit your needs.

  • Re: Insert and Delete Rows with formula and formating in a table using VBA


    Kieran,


    The set up is such that the user will input their own data, I am technically designing a place for them to input their data.


    What i know is that the column headings will remain the same and the data in the 'world' column will remain the same, everything else can be changed apart from the format and formulas.


    The reason i want to use VBA is so there is some restriction on what and how data can be added, else I fear the outputs from all users will be slightly different causing error in the long run when data needs to be complied to one worksheet say.

Participate now!

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