VBA Script adding rows on a worksheet from value on a different sheet

[WyldKnyght]

Hello,

I'm new to VBA scripting, and have tried a few versions posted here, but none seem to work for my layout.

I'm trying to make a sorted grocery list that calculates the lowest price.

I've sorted out all the formulas to get the information, what I can't do is add lines, so the list is cut off, until I manually add rows.

This is what I have so far.

This chart adds the stores in the list and the number of items.

Shopping Value Finder.xlsm
	AA	AB
2	Store	Items
3	Atlantic Superstore	2
4	Giant Tiger	2
5	Murphy's Pharmacy	1
6	Sobey's	2
7	Walmart	8
8
9
10
11
Calculations

Cell Formulas
Range	Formula
AA3:AA7	AA3	=UNIQUE(FILTER(T3:T300,T3:T300<>""))
AB3:AB11	AB3	=COUNTIF($T$3:$T$300,AA3)
Dynamic array formulas.

On Sheet2:Summary

Shopping Value Finder.xlsm
	A	B
1	Grocery Store Shopping List
2
3	Store:	Atlantic Superstore
4		Chocolate (Marzipan)
5		Flex Straws
6
7	Store:	Sobey's
8		Chicken Broth
9		Pizza Pockets (Meat Lover's)
10
11	Store:	Giant Tiger
12		Pizza Pockets
13		Chocolate (Sea Salt/Caramel)
14
15	Store:	Murphy's Pharmacy
16		Atoma Pain Relief Gel
17
18	Store:	Walmart
19		Devil's Food Cake Mix
20		Golden Yellow Cake Mix
21		Mr. Noodles (Chicken/Spicy Chicken)
22		Vanilla Extract
23		Laundry Stain Remover
24		Eggs (Large)
25		Italian Seasoning
26		International Delight French Vanilla
27
Summary

Cell Formulas
Range	Formula
A3	A3	=IF(B3 <>"", "Store:", "")
B3	B3	=IF(Calculations!AB3>0,Calculations!AA3,IF(Calculations!AB4>0,Calculations!AA4,IF(Calculations!AB5>0,Calculations!AA5,IF(Calculations!AB6,Calculations!AA6,IF(Calculations!AB7,Calculations!AA7,IF(Calculations!AB8>0,Calculations!AA8,IF(Calculations!AB9,Calculations!AA9,IF(Calculations!AB10,Calculations!AA10,IF(Calculations!AB11>0,Calculations!AA11,)))))))))
B4:B5	B4	=FILTER(Calculations!U3:U300,Calculations!T3:T300=B3,"")
A7,A18,A15,A11	A7	=IF(B7 >"", "Store:", "")
B7	B7	=IF(B15<>"", IF(Calculations!AB6,Calculations!AA6,IF(Calculations!AB7,Calculations!AA7,IF(Calculations!AB8>0,Calculations!AA8,IF(Calculations!AB9,Calculations!AA9,IF(Calculations!AB10,Calculations!AA10,IF(Calculations!AB11>0,Calculations!AA11,)))))),"")
B8:B9	B8	=FILTER(Calculations!U3:U300,Calculations!T3:T300=B7,"")
B11	B11	=IF(B3<>"",IF(Calculations!AB4>0,Calculations!AA4,IF(Calculations!AB5>0,Calculations!AA5,IF(Calculations!AB6,Calculations!AA6,IF(Calculations!AB7,Calculations!AA7,IF(Calculations!AB8>0,Calculations!AA8,IF(Calculations!AB9,Calculations!AA9,IF(Calculations!AB10,Calculations!AA10,IF(Calculations!AB11>0,Calculations!AA11,)))))))),"")
B12:B13	B12	=FILTER(Calculations!U3:U300,Calculations!T3:T300=B11,"")
B15	B15	=IF(B11<>"", IF(Calculations!AB5>0,Calculations!AA5,IF(Calculations!AB6,Calculations!AA6,IF(Calculations!AB7,Calculations!AA7,IF(Calculations!AB8>0,Calculations!AA8,IF(Calculations!AB9,Calculations!AA9,IF(Calculations!AB10,Calculations!AA10,IF(Calculations!AB11>0,Calculations!AA11,))))))),"")
B16	B16	=FILTER(Calculations!U3:U300,Calculations!T3:T300=B15,"")
B18	B18	=IF(B7<>"", IF(Calculations!AB7,Calculations!AA7,IF(Calculations!AB8>0,Calculations!AA8,IF(Calculations!AB9,Calculations!AA9,IF(Calculations!AB10,Calculations!AA10,IF(Calculations!AB11>0,Calculations!AA11,))))),"")
B19:B26	B19	=FILTER(Calculations!U3:U300,Calculations!T3:T300=B18,"")
Dynamic array formulas.

Any help and suggestions greatly appreciated

Thanks
C.

 

[WyldKnyght]

I'm also looking from the opposite side, and trying to figure out how to add rows when a #SPILL! error occurs