arrays - VBA excel row filter and merge column cells loop on condition -

i’ll start spread sheet layout , explain in steps i’m trying achieve, i’ve tried code wise far have put far fails

my small version of spread sheet attached full spread sheet 17mb , alot of rows test-full.xls

[spreadsheet1.png]

it consists of

make,model,size1,size2,size3,fuelsystem1,fuelsystem2,fuelsystem3,years,filterref

the actions want replicate in code first filter make, model, filter ref, fuelsystem1 (petrol)

[spreadsheet2.png]

i use these autofilters match filter year ranges if size falls year range merged (copied next free size column of same year range)

i delete rows empty size data [spreadsheet4.png]

[spreadsheet3.png]

the last filter (fuelsystem1) done second time filter diesel or other fuel types , previous step of merging size year again go on filter next filterref apply sizes year ranges resulting spreadsheet

[spreadsheet result.png]

i go on filter next model in model column within make , repeat above steps on model repeat above next make in make column did consider use of pivot table don’t know how manipulate them in vba (this first time doing more simple function in vba) if cant figure out in vba i’m going go python save day :)

code have tried

i have tried find way loop through criteria’s of autofilter seems vba has no built in function perform criteria:=array(=criteria(n))

so created separate table unique values use criteria in separate sheet. thought load these array , pass them search criteria , loop on each filter right way tackle , trying work out how can search through them

   activesheet.range(“a:i”).autofilterfield:1,_         criteria:array(),operator:=xlfiltervalues  

so idea load following arrays

make model filter fuel

dim makearray variant dim totalrows long dim makeloop integer  totalrows = rows(rows.count.end(xlup).row makearray=range(cells(1,1)cells(totalrows,1)).value      makeloop = lbound(makearray) ubound(makearray)          sheets(“sheet1”)              .autofiltermode = false             .usedrange.autofilter             .usedrange.autofilter field:=1, criteria1:=makearray(makeloop) , operator:=xlfiltervalues          end      next makeloop  

should nest loops or vba have better ways this

this intention

loop through search criteria makearray() set filter range ¬ loop through modelarray() set filter range ¬ loop through filterarray() set filter range ¬loop through fuelarray() set filtered range

look @ range , work out date ranges fall each other copy size each these return should go in next filterarray() loop

is possible vba? program in python considering route thought i’d check vba first built excel , therefore should have more control on spreadsheet (i’d have thought) although can’t autofilters criteria list or remove duplicates array , noticed have declare length of array first have count rows

should go old python have no idea when comes vba

sorry if snippets of code bit broken i'm on centos laptop @ moment no excel handy

the link spreadsheet , images wont let me post on account

https://drive.google.com/open?id=0bxbxzkx_2gogm0dmb2durzrialu