This project has moved and is read-only. For the latest updates, please go here.

FilteredRange using non-contiguous data

Aug 18, 2013 at 6:27 AM
Edited Aug 18, 2013 at 6:29 AM
I have been trying to select a range of data from my Excel solution using the Worksheet.AutoFilter.Range property.

Much to my disappointment, I discovered this still returned all cells, even ones I had filtered out.
I then used Worksheet.AutoFilter.Range.SpecialCells(Excel.Enume.xlCellTypeVisible) which returned a range of all visible cells, but that were contiguous (ie, next to each other). Any rows from the filter that were not adjacent, even though visible were not included in the returned range.

It seems the only way I can get the results I need is to perform a loop on all rows in a range from say Worksheet.UsedRange and check the Range.Hidden property for each row, using something like shown below:
foreach (Excel.Range row in worksheet.UsedRange.Rows)
            {
                if ((bool)row.Hidden == false)
                {
                    iCount++;
                    filteredRange.Add(row);
                }
            }
While this would work, I know it is very slow. Before I decided to update my code to allow for filtered results, I was previously using object[,] myValues = (object[,])worksheet.UsedRange.Value and looping through this to investigate my values - this was very fast.

Any ideas as to how we can do this with similar performance to mySheet.UsedRange.Value?

....as a side note, for some reason I seemed to get the same results with worksheet.UsedRange and worksheet.AutoFilter.Range - even with a filter applied in Excel.
Aug 20, 2013 at 5:59 AM
hey bob, not sure how i can help you. where you from?
Aug 20, 2013 at 11:51 AM
Hey Sebastian,

OK, looks like I will have to opt for a 'slow' but workable solution for now and then maybe revisit my performance issues later.

I am from Australia, how about you?

I would also like to say how impressed I am with your NetOffice solution, very elegant and great to use!
Your provided examples and tutorials are also fantastic, great job.

You are a real asset to the open source community!
Aug 20, 2013 at 12:23 PM
sometimes its easier for me to talk in german. (thats wyh i ask) i start comunity thread in the excel community to see what i can do for you but without a good answsers. some people told me to check for the row height 0 instead of hidden but i think its not helpful for you.
note sure the xlCellTypeVisible filter is not helpfull for you? as far i',m understand you want filtering any hidden rows and columns right ???