Advance Filters AND criteria populated from selected items in listboxes

[Lenna]

Hello,

There are three list boxes on a sheet(Instruments,Months,Years). After the user selects items in each list box, selected items from each list box are copied into arrays and outputted into corresponding columns to be used as AND criteria for an Advanced filter. This set up works only when the user selects equal number of items from each listbox. The problems arise when the user doesn’t select an equal number of Listboxes and the Advance filter fails, because all of the expressions need to be in the same row for implicit And operator to combine the expressions. Please suggest how to fix this problem using Excel VBA?

Thanks for your help in advance

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]InstrumentSN(criteria)[/TD]
[TD]Value[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]LX124R[/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]HDR345[/TD]
[TD][/TD]
[TD]Aug[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]UD4566[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]InstrumentSN(data)[/TD]
[TD]Value[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]LZ124R[/TD]
[TD]34.23[/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
</tbody>[/TABLE]

 

[Lenna]

Clarification:

Top table shows the output from listboxes. The bottom table represent the correct criteria for advanced filter that i need to configure from the listboxes output. any advice would be Greatly appreciated. Thanks


[TABLE="width: 292"]
<tbody>[TR]
[TD]InstrumentSN[/TD]
[TD]Value[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]XDF1234[/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]GR12345[/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RW2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]InstrumentSN[/TD]
[TD]Value[/TD]
[TD]Month[/TD]
[TD]Year[/TD]
[/TR]
[TR]
[TD]XDF1234[/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]GR12345[/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]RW2345[/TD]
[TD][/TD]
[TD]Jun[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]XDF1234[/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]GR12345[/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]2015[/TD]
[/TR]
[TR]
[TD]RW2345[/TD]
[TD][/TD]
[TD]Jul[/TD]
[TD]2015[/TD]
[/TR]
</tbody>[/TABLE]