+ Reply to Thread
Results 1 to 4 of 4

Insert / Delete Rows depending upon Dynamic Built Table

  1. #1
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    295

    Question Insert / Delete Rows depending upon Dynamic Built Table

    I have a long macro that builds a heavily formatted table and builds it under headers within a table located at a dynamic spot. The user will input data into userforms which determinds how big / small this built table will be. Sometimes it might be bigger than the "open" cells / rows and sometimes it might be smaller than the open cells. When I say open cells, I mean the open or blank rows between the headers and the TEXT data that I have below the table. This text data will always be there.

    All I want is the "TEXT" data to move and always be (2) rows below the data that is entered from the macro.

    So I need to insert / delete rows depending upon how big /small the built table is and depending upon how many open cells there are when the user opens the worksheet, as that number will be different each time they open it.


    And I cant just copy and paste , (or have a macro "move" the text data) because that text data is heavily formatted with merged cells and other items. It is basically a word document brought over into excel.


    I have a sample document that shows just what I am talking about. Sheet 1 is all there is to work with. The other tabs are examples of "results" that would result when the macro would finish.

    Thanks SOOO MUCH for looking at this.
    Attached Files Attached Files
    Last edited by clemsoncooz; 02-10-2012 at 11:45 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    12-05-2011
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010 & 2013
    Posts
    308

    Re: Insert / Delete Rows depending upon Dynamic Built Table

    Hi Clemsoncooz

    Where is the built table stored? Is it in another sheet of the same workbook?

    My approach would be to read the built table, insert/delete the correct number of rows, then copy it in. Or, if I'm building it in the existing sheet, start with the 2 spare rows and insert additional rows as I went.

    The VBA code to insert a row on Sheet 1 at Row 24 is:
    Please Login or Register  to view this content.
    To delete a row
    Please Login or Register  to view this content.
    To delete the blank rows, where there are more than 2, the following code would do it:
    Please Login or Register  to view this content.
    I'm not sure how to deal with the converse - where there are too few rows - without answers to my questions above. You could just insert 1000 rows (to be sure) then run the above code to delete them again once you've built your table. Not very efficient, but it would work:
    Please Login or Register  to view this content.
    Best regards, Rob.
    Last edited by rscsmith; 02-09-2012 at 06:15 PM.

  3. #3
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    295

    Re: Insert / Delete Rows depending upon Dynamic Built Table

    Hi rscsmith and thanks for looking at this. I really appreciate your pointers.

    "Where is the built table stored? Is it in another sheet of the same workbook?" The table is built within the same workbook. It is built on the same sheet that the text data resides, just like the sample workbook attached in first post.

    "My approach would be to read the built table" - Perhaps my approach of building the table within that worksheet is NOT the way to go, because I can't read it before it is placed within the worksheet. If the table is bigger than the open cells, it trys to overwrite what is current in the text data below the open rows.

    I will play around with this and see what I cant come up with.

    Thanks again.

  4. #4
    Forum Contributor
    Join Date
    12-20-2011
    Location
    United States, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    295

    Re: Insert / Delete Rows depending upon Dynamic Built Table

    For anyone who might look at this another time - I took the less efficient way.

    Before my table is built, I add about 200 rows with:
    Please Login or Register  to view this content.
    Then later on in the code, I delete rows based upon my selection of open rows with:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1