Hi,
I am a complete novice with VBA. I normally try and use formulas in cells to get through. However I think my latest problem has stumped me, and is beyond the scope of a formula in a cell.
What I really need is a code to add an extra row under a dynamic table as it expands, so that it is always separated from any data below it.
Overview - I have a dynamic table with Staff details and their leave entitlements for the year.
I also have some analysis data below the dynamic table.
In real life there are more than 30 staff per sheet/location, and 5 different rows of data analysis underneath. However I've tried to keep it simple in the example.
I need the staff table above to remain dynamic as there are hidden formulas and other formatting I need to expand with the table as we add/remove staff members.
The problem is that obviously dynamic tables are only able to expand as far as the empty rows below it. Once it reaches the analysis data, it stops expanding dynamically. So what I really need is a code to add a row under the dynamic table as it expands, so that it is always separated from the data below it.
I have tried adding a code that when column "A" exceeds X amount of entries (10 in the sample data), it'll add another row automatically. This hasn't worked.
Can anyone please help?
Thanks in advance!
ETA - Here is what I tried
Sub MyInsertRow()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
If lastRow >= 10 Then
Rows(lastRow + 1).Insert
End Sub
Bookmarks