Filter list to VBA array

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Good morning,

Hope somebody can help me with this or let me know if it's possible.

I'd like to use advanced filter to get a list of unique values in a range but instead of writing this list to a location in the worksheet I'd like to load the values directly into an array.

Has anyone managed this before?

What I tried to use was:

Code:
    Range("A1:A5474").AdvancedFilter Action:=xlFilterCopy, _
            CopyToRange:=Array1, Unique:=True

I'm getting a "Reference not valid" message so I was hoping someone knew of a workaround.

Thanks

Nick
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hi,

I don't believe it's possible to filter directly to an array variable. The help says CopyToRange Optional Variant. The destination range for the copied rows if Action is xlFilterCopy.

My first thought was to use your code and then use SpecialCells to just populate the array. However, this only seemed to pick up the first cell of each area in the visible range. My next attempt, which seems to work, is just to filter the range and copy it below the list and then use that new list to populate the array. Is that clear?!

Anyway, here's the code I used:-

Code:
Sub CopyToRange()
Dim Array1

Range("A1:A5474").AdvancedFilter Action:=xlFilterCopy, Unique:=True, CopyToRange:=Range("A6000")
Array1 = Range("A6000:A" & Range("A65536").End(xlUp).Row).Value
Range("A6000:A65536").Clear 'Get rid of the filtered data
 
End Sub

Obviously, you could get rid of the hard coded references and use more sophisticated methods to find the start and end of your ranges.
 
Upvote 0
Thanks DK.

The hard coded ranges are just temporary until i can get it working as I want. For now I've used a similar approach by adding a column temporarily to the right of the data and then deleting it after i've loaded the values into the array but it may be better using your method and having the data underneath. I'll have to experiment.

Thanks again

Nick
 
Upvote 0
I don't know whether this is a good method, but it seems to work and avoids the copying and deleting:

Code:
Sub test()
    Dim TestRg As Excel.Range
    Dim Array1(20) As Variant
    Dim i As Integer
    i = 2
    
    Set TestRg = Range("A1:A20")
    TestRg.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("E1:E2"), Unique:=True
    For Each c In TestRg.SpecialCells(xlCellTypeVisible)
        Array1(i) = c.Value
        i = i + 1
    Next c

End Sub
 
Upvote 0
Hi psitaram,

thanks for the response.

for some reason I couldn't get that to work. It was only adding the filter header into the array. Thanks for the response though.

I'm going to go for adding the data at the bottom and then clearing those cells (thanks again dk) as that seems to work well. Think I'll stick with the "if ti ain't broke, don't fix it" thoery for now. However, I'd be interested to get it working just for future reference if you could provide some feedback.

Regards

Nick
 
Upvote 0
Following procedure returns a array having unqique values ..but while writing this macro I was amused to see i did not have to increment integer value while writing redim perseve..please see the details below the AMUSED LINE... I hope forum will have some idea about this.
'Can I get views about this why it happens..."



Sub getuniqueinarray()
Dim rinput as range
Dim strdata() As String
Dim intdata As Integer
Dim intunq As Integer
Dim c As Range
Dim ca As Variant
Dim tmp
Dim struni() As String
Dim blnf1 As Boolean



Set rinput = Range("s16:s34")

For Each c In rinput
ReDim Preserve strdata(intdata)
strdata(intdata) = c.Value
intdata = intdata + 1
Next c

' We will not need to sort data.
'Module1.sortdata strdata()
' getting first unqique valu from strdata()
'
ReDim struni(intunq) ' if we want to allocate value to array we need to redim first
struni(intunq) = strdata(0) 'first entry from strdata()array

For intdata = LBound(strdata()) To UBound(strdata())

tmp = strdata(intdata)

For intunq = LBound(struni()) To UBound(struni())
If struni(intunq) <> tmp Then
blnf1 = True
Else
blnf1 = False
End If

Next intunq

If blnf1 = True Then
'intunq = intunq
' AMUSED>>> WHY IT HAPPENS LIKE THIS>

ReDim Preserve struni(intunq) ' INTERESTING THING IS HAPPENING HERE
' ARRAY AUTOMATICALLY RESIZES BY ONE..WE NEED NOT NEED TO ICREMENT THE INTEGER VALUE --

'Can I get views about this why it happens..."


struni(intunq) = tmp
'blnflag = False gived wrong results..so not need to write
End If

Next intdata

For intunq = LBound(struni()) To UBound(struni())
Debug.Print struni(intunq)
Next intunq

' FOR EACH... NEXT.. LOCKS THE ARRAY
' For Each ca In struni()
' If ca <> tmp Then
' intunq = intunq + 1
' ' ReDim Preserve struni(intunq) ' we cannot redim array in a cell as array is locked when using for each c next c loop
' ' struni(intunq) = ca
' End If
' Debug.Print struni(intunq)
' Next ca
'===================================================
'if we want sorted result of unique values sort here..
' sortdata struni()

End Sub
 
Upvote 0
I tested the code further with some changes and found that I could get all the filtered values through the Array:


Code:
Sub test()
    Dim TestRg As Excel.Range
    Dim Array1(20) As Variant
    Dim i, j As Integer
    i = 1
    
    Set TestRg = Range("A1:A20")
    TestRg.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("B1:B2"), Unique:=True
    For Each c In TestRg.SpecialCells(xlCellTypeVisible)
        Array1(i) = c.Value
        i = i + 1
    Next c
    j = i - 1
    i = 1
   
    ActiveSheet.ShowAllData
    For Each c In Range("C1:C" & j)
        c.Value = Array1(i)
        i = i + 1
    Next c
End Sub

Sitaram
 
Upvote 0
wouldn't this be too slow? Is there another method of adding directly an autofiltered range to the array ?
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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