vba - Performance hit when selecting dynamic menu items for an Excel custom UI -

i have stitched excel workbook dynamically populates custom ui drop down menus.

here's code populates menu

sub getfilterbyteam(control iribboncontrol, byref content)  content = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui""><button id=""team1"" label=""team1"" onaction=""filterteam""/><button id=""team2"" label=""team2"" onaction=""filterteam""/><button id=""team3"" label=""team3"" onaction=""filterteam""/><button id=""team4"" label=""team4"" onaction=""filterteam""/><button id=""team5"" label=""team5"" onaction=""filterteam""/><button id=""team6"" label=""team6"" onaction=""filterteam""/></menu>"  end sub 

when select value in dynamicmenu macro fired. macro grabs id of dynamic button

here's code

sub filterteam(byref control iribboncontrol)      on error resume next     worksheets("filter").listobjects("filtertable").databodyrange.rows.delete      call addtofilterlist(paramteamname:=replace(control.id, "_", chr(32)))     call filter  end sub 

short explanation of above

1. on error skip on errors table "filter" empty
2. addtofilter explained below
3. filter runs advancedfilter macro on main table criteria in table populated addtofilter

here's problem.

performance great first item in dynamicmenu. rounds down 0 milliseconds. however, other buttons in dynamicmenu have performance on order of 200 milliseconds.

by measuring calls, identified

call addtofilterlists(.... 

part what's causing. i've tried passing simple string (for testing purposes) instead of control.id doesn't help

i @ lose. think i'm making small mistake can't figure out life of me.

here's code addtofilterlist

sub addtofilterlist( _     optional param1 string = "", _     optional param2 string = "", _     optional param3 string = "", _     optional param4 string = "", _     optional param5 string = "", _     optional param6 string = "", _     optional param7 string = "", _     optional param8 string = "", _     optional param9 string = "", _     optional param10 string = "", _     optional param11 string = "", _     optional param12 string = "", _     optional param13 string = "", _     optional param14 string = "")      dim lo listobject     dim onewrow listrow      set lo = sheets("filter").listobjects("filtertable")      set onewrow = lo.listrows.add(alwaysinsert:=true)      onewrow.range.cells(1, column1).value = param1     onewrow.range.cells(1, column2).value = param2     onewrow.range.cells(1, column3).value = param3     onewrow.range.cells(1, column4).value = param4     onewrow.range.cells(1, column5).value = param5     onewrow.range.cells(1, column6).value = param6     onewrow.range.cells(1, column7).value = param7     onewrow.range.cells(1, column8).value = param8     onewrow.range.cells(1, column9).value = param9     onewrow.range.cells(1, column10).value = param10     onewrow.range.cells(1, column11).value = param11     onewrow.range.cells(1, column12).value = param12  end sub 

commenting out of onewrow = lines helps can't figure out why performance dependent on value select in dynamicmenu. , effect repeatable , quantifiable.

maybe change (not tested) - send row range object

option explicit  public sub addtofilterlist(byref rng range)      dim lo listobject, nr long, rc long      if rng.rows.count = 1 , rng.columns.count <= 12          worksheets("filter")              set lo = .listobjects("filtertable")              nr = lo.databodyrange.row + lo.databodyrange.rows.count 'new row below table              rc = rng.columns.count - rng.column              .range(.cells(nr, lo.databodyrange.column), .cells(nr, rc)).value2 = rng.value2          end     end if end sub