VBA: Getting an array from a filtered range

[lepico]

Hi everyone, this i smy first post on this site, I hope someone will be able to help:

I'm trying to create a function to extract data from a filtered sheet, without having to loop through all rows.

I hoped the .SpecialCells(xlCellTypeVisible) would do the job, but it seems only to return the first area.

Here's the first code I tried:

Function GetFilteredData(ByRef Ws As Worksheet) As Variant
        Dim I As Integer
        Dim J As Integer
        With Ws
            I = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
            J = .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
            GetFilteredData = .Range(.Cells(1, 1), .Cells(I, J)).SpecialCells(xlCellTypeVisible)
        End With
End Function

I was hoping that maybe something with the application.index function may work, using stuff I saw in this thread:

http://www.mrexcel.com/forum/excel-questions/564473-transfer-multiple-ranges-one-array.html

But so far, I couldn't manage to get it working.

Any ideas? (not sure if it's event possible...)

Thanks in advance guys!

 

[MickG]

This worked for me:-

Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
MsgBox GetFilteredData(Sheet2).Address
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Function GetFilteredData(ByRef Ws [COLOR="Navy"]As[/COLOR] Worksheet) [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] I [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] J [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
        [COLOR="Navy"]With[/COLOR] Ws
            I = .Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
            J = .Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
            [COLOR="Navy"]Set[/COLOR] GetFilteredData = .Range(.Cells(1, 1), .Cells(I, J)).SpecialCells(xlCellTypeVisible)
        [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[lepico]

Hi, thanks for this answer,

Sorry, I guess I didn't make myself clear at the beginning: I want this function to return a 2-dimensional array, not a range

Any ideas?

Rgds