0

Good morning,

I am using the range method AdvancedFilter:

public object AdvancedFilter (Microsoft.Office.Interop.Excel.XlFilterAction Action, object CriteriaRange, object CopyToRange, object Unique);

In criteria, I give a range like Range["A1:B2"]. This working well.

But, there is an alternative to give a range for the criteria? I mean, it is possible to create an array or something like this, and passing these to the criteria AdvancedFilter?

Thank You!!!

Murru Stefano
  • 21
  • 4
  • Advanced Filtering allows dynamic formula in the source range, so it’s unlikely to allow data arrays in the parameter. You could try it in the Excel UI yourself, if you can get the function formula to accept a literal array, then the API should as well. – RBarryYoung May 05 '21 at 17:57
  • You could also try it from VBA in excel, to see if it will accept an array in that parameter. Then if it does, you can start on the problems of how to pass an array from c#, through the interop. VBA can natively make and pass the specific type of array that Excel accepts (2d, variant array of variants), so that’s an effective way to test for this. – RBarryYoung May 05 '21 at 18:02
  • Ah, OK. This answer explains that you *can* do this from VBA with AuotFilter instead of AdvancedFilter, https://stackoverflow.com/a/34532975/109122. Now all you have to do is figure out how t create and pass the array from C#. – RBarryYoung May 05 '21 at 18:08
  • Thank you @RBarryYoung !!!! It is the first time that I see that we can use dictionaries into the criteria of Excel filters. You are very kindly. There is any chance to convert a so valued range in a new a dictionary?: wsCriteria.Range["A1"].Value = "Head"; wsCriteria.Range["A2"].Value = "<>start"; wsCriteria.Range["B1"].Value = "Head"; wsCriteria.Range["B2"].Value = "<>end"; – Murru Stefano May 05 '21 at 22:24

0 Answers0