VBA: Getting an array from a filtered range

lepico

New Member
Joined
Jun 16, 2016
Messages
2
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:

Code:
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:
HTML:
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!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
This worked for me:-
Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top