Smartest way to update a list

[Jacc]

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!

[jeffreybrown]

You could use something as simple as...

Please Login or Register  to view this content.

Replace the part in red with the formula you want.

[RHCPgergo]

Hi,

I would go with option two. Since the list is populated with VBA, you can populate the next column with the formula at the same time. If you post the code and the formula, I can have a look, it sounds simple.

[Jacc]

Thanks for fast reply! I'm leaning towards the VBA solution and the snippet jeffrey provided.
What about the shrinking part? I know there is a ton of there but I haven't processed it all yet...

If the Table feature would have the intelligence to shrink that would be awesome but for some reason it hasn't...

[jeffreybrown]

For the shrinking part you just need to rerun the macro once values from column A have shrunk.

Please Login or Register  to view this content.

[Jacc]

This is what I have so far, totally contradicting what I thought I would have by now. Getting feedback from people who know a thing or two about Excel is still a much needed stimulation at the end of the day.
I guess I'm a sucker for using Tables, the formulas come out so nice with all the correct ranges! Especially when using things like COUNTIF that covers the whole column.

Please Login or Register  to view this content.

I'm sorry for being boring and not posting a workbook. It is rather big and links back and forth so peeling of the unnecessary (and potentially sensitive parts) seemed like to much work right now. Sorry!

More correctly there are three columns. The B column is populated by VBA (actually list of files in a folder). The A column just extracts the name without filename extension, the other one checks for doubles (someone may put in the same pic name in jpg and png). Not the most crucial function now that I think about it...

So what I do is delete all rows except the top data row and header. I then clear the last data in cell B2 which leaves me with an empty table that will automatically copy down the adjacent formulas.

Still, I'm only semi happy with how it turned out. If anyone wanna shoot some wholes in my grand plan, feel free. Or just comment about this whole Table thing. Are they slow or something or way is it that it does not seem that popular?

[Jacc]

Actually, I think I'm gonna start a new thread on pros and cons with Tables, hold on and I'll post a link.

[Jacc]

http://www.excelforum.com/excel-gene...el-tables.html

There. It's up to you to decide which thread is more suitable for your post. I hope this will keep things tidy and in line with forum policy.

[Jacc]

Quite some time has passed and I don't recall what happened in between. I think I found out that my Table solution was slow (could be due to other factors though, not sure) and I ended up using the solution suggested my jeffrey. Several updates has passed and everything works fine, I'm happy with it.

[jeffreybrown]

Glad to hear you have a good working solution