Thread: 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.

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:

Sheets("Sheet1").Rows(24).Insert

To delete a row

Sheets("Sheet1").Rows(24).Delete

To delete the blank rows, where there are more than 2, the following code would do it:

Sub DelBlanks()
Dim cel As Range

For Each cel In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("H:H"))
'Column H appears to have data in every row, so use this.  Column A wouldn't work, as sometimes it is blank (merged cell) 

 If cel.Value = "" Then  'We've found the first blank row
  Do
  'delete the blank row beneath (row+1) - keep doing this until Row+2 <> blank
  Rows(cel.Row + 1).Delete
  Loop Until cel.Offset(2, 0) <> ""
  Exit Sub
  End If
  Next cel
 
End Sub

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:

Activesheet.Rows(24).Resize(1000).Insert

Best regards, Rob.