VBA to insert or delete rows in named range based on spill range

[markkeith]

Cross posted at VBA to insert or delete rows in named range based on spill range

I have a named range "nameList" (B3:E20) that are populated by a spill range from dynamic array formula in cell B3, and there's a table just below cell B24. If the spill range row count are less or more than the number of rows of nameList then I want the unused cell to be deleted or insert new rows if not enough, Basically I would like the nameList to be resized dynamically based on spill range.

Spill range data are sometimes 2 rows only (too much unused rows) or up to 50 rows (spill error due to the table below) that's why I want the nameList to be resized

I've been looking for similar topic for numerous hours now but still no luck.

 

[Kaper]

If I understand well, the equivalent to your request is to create dynamically located "bottom table" when the size of "top table" changes.

Probably it changes because of some changes in other cells. so yoy can 1) identify on which cells the size of "top table" depends.
And then add the event handler to Change event to the cells, on which formula in B3 depends. And the focation of your "bottom" formula (B24) shall be changed in this event handler proceduire.

Two other (may be too simple ???) ideas would be to:
- place tables next to each other (if the width of the first is constant(or almost constant))
- or calculate both tables in a separate (may be hidden) sheet(s) and then just present their content together in one sheet (they are rather small so functions like INDIRECT, OFFSET etc) would not slow down your wortkbook

 

[markkeith]

If I understand well, the equivalent to your request is to create dynamically located "bottom table" when the size of "top table" changes.

Probably it changes because of some changes in other cells. so yoy can 1) identify on which cells the size of "top table" depends.
And then add the event handler to Change event to the cells, on which formula in B3 depends. And the focation of your "bottom" formula (B24) shall be changed in this event handler proceduire.

Two other (may be too simple ???) ideas would be to:
- place tables next to each other (if the width of the first is constant(or almost constant))
- or calculate both tables in a separate (may be hidden) sheet(s) and then just present their content together in one sheet (they are rather small so functions like INDIRECT, OFFSET etc) would not slow down your wortkbook

Yes the dynamic array formula in cell B3 will be triggered by Worksheet_Change event when certain cell data changes in Active sheet.
I'm thinking of your 2 suggested approach and will try option 1. For option 2, it's not ideal to place the tables next to each other because "top table" columns might expand or shrink as well.

Could you please write a sample code on how to count the spill range rows and then change the location of "bottom table" accordingly.

 

[markkeith]

SOLVED at: VBA to insert or delete rows in named range based on spill range