How do I get data from an Excel Autofilter Range?

[Ken_Naylor]

I have a spreadsheet with a large number of rows (5000) from which I want to extract data.  I have successfully opened The Workbook, Setup an autofilter and filtered the data to select the data matching the criteria using the invoke and property nodes of the Excel ActiveX object.  I now need to get the selected data into an array for further processing and this is where I get stuck.  Excel help online seems to suggest creating a Range Object using special cells with the type xlCellTypeVisible.  If I Range.Count I get the right number of cells but Range.Value2 produces a variant which only contains the first block of data (An autofiltered Range seems to be made up of several non continuous blocks).  Does anybody know how I can read the contents of the entire range into an array of strings?

Thanks in advance

Ken

[rsw]

Hi Ken,

You need to use the range and get text properties of the ActiveX class in order to read out the data in a string array format, the attached snipped shows these properties.

Hope this helps!

-rsw

[Ken_Naylor]

Thanks for your answer but it does not help.

I could not work out why Numeric1 and Numeric2 were concatenated with 'A' and wired to Cell1 and Cell2.

You may have missed the point of my question.  The snippet below is a very elegant way of getting the text form each cell and putting it in an array of strings which is what (I think) your snippet does.  However this only works if the range is continuous e.g.. A2:G9 (7 rows by 7 columns).  The Autofiltered range (with SpecialCells Type xlCellTypeVisible) is non continuous e.g.. A2:G3, A6:G10, A15:G17 (10 rows by 7 columns).  In this example the snippet only returns A2:G3

Another way round may be to get the row numbers of the cells in column A of this non continuous range and read them in one row at a time.  Any thoughts on how this may be achieved?

Thanks for your interest

Ken

[Ken_Naylor · Accepted solution]

SOLVED IT!!!

Found a property call areas which returns a collection of areas each one a continuous range object.  Simply cycled through these areas using the earlier technique building the array as I go.  See below

Thanks

Ken

PS  can I KUDOS myself

[WangDerek]

I met the same problem.Can you share how do you solve the problem. Thank you very much!

[Ken_Naylor]

It was a while ago when I did this but found it eventually

Good Luck

Ken

[WangDerek]

Thank you very much for helping me.