Hi

I have a list which is populated with VBA. It consists of only one column of data. In the adjacent column I have a formula that I want to extend as many rows as the list of course. The list can both grow to a couple of thousand lines and shrink back to just the header line. I also like to shrink the used range to limit the workbook size.
Does anyone have a good recipe for how to handle this?

Option 1:
Just copy the formula down as far as you ever expect the list to extend, maybe 10 000 rows or so. This is simple but a waste of space and resources. Also I already burnt myself a few times by underestimating the size of the list. Also the used range remains large.

Option 2:
Use VBA to copy the formula whenever the list is updated and delete lines if the list shrinks. Hmm...

Option 3:
Use the Excel Table feature. This takes care of extending the formulas as far as the list goes but it has no "shrink" feature as far as I know. I was playing with the idea of having the top row hidden so that the formulas remain even if all data is cleared. This would again require some VBA coding.

Other suggestions are very welcome!