Excel VBA code to get Autofiltered data to array

[sskannan]

Hi All,

I hope someone may be able to help please

I have an Excel sheet with range A1:L42 contain various data

the first row is the hedding row with Auto filter set

I can set the Autofilter programatically, but I am unable to get that filtered data into an array or collection.

Could any body know how to do this, please help me.

Thanks,
Khan

[Bob Phillips]

Dim LastRow As Long
Dim i As Long
Dim sh As Worksheet
Dim rng As Range
Dim ary As Variant

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng = Range("A1:L1").Resize(LastRow).SpecialCells(xlCellTypeVisible)

End With
Application.ScreenUpdating = False
Set sh = Worksheets.Add
rng.Copy sh.Range("A1")
ary = sh.Range(sh.Range("A1"), sh.Range("A1").End(xlDown)).Resize(, 12)
Application.DisplayAlerts = False
sh.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

[sskannan]

Hi James,
Thanks very much for your help.

Is that possible to get the data into an array without add a new sheet ?

Please let me know.

Thanks,
Khan

[sskannan]

Hi James,

Also I don't need the Headdings in the array, need only tha filtered data , Is that possible to get that.

Thanks,
Kan

[sskannan]

Hi James,
I sort that header problem, the only bit I would like to know is to get the data into array with out add and delete new work sheet.

Is that possible ?

Thanks,
Khan

[Bob Phillips]

Why is it an issue, we delete it afterwards so it is temporary.

[sskannan]

Hi James,

The problem is I need to use this array to fill the form combo boxes.

I need to get the Unique values for the filtered rows and fill the combo for user selection.

I am just thinking any easy way out for this

Thanks,
Khan

[Bob Phillips]

This can be used in that way. What problem are you envisaging?

[sskannan]

Hi James,

Some time LastRow return 1

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

I could not find the reason , do you know why ?

Thanks,
Khan

[Bob Phillips]

Without seeing the data, I can only guess that there is only one row.