Issue with Excel automation in C#

Question

I am dynamically creating an excel file with some list options using C#. There will be 3 or more columns with drop down list. Now the issue is that in some columns there will not be values in some rows. But the drop down list shows all the empty column values(empty) also.

Is there a method by which I can filter the excel column with all non empty values?

Sample Code

Range r = (Range)ExcelWS.Columns.get_Item(1, Missing.Value);
r.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, Missing.Value, "=$A:$A", Missing.Value);
r.Validation.ErrorMessage = "Please select from available options";
r.Validation.IgnoreBlank = true;
r.Validation.ShowError = true;
r.Validation.InCellDropdown = true;

Answer 1

You should look into AutoFilter

Example usage can be found on this old post: http://blogs.msdn.com/b/erikaehrli/archive/2005/10/27/excelmanagedautofiltering.aspx