Filter list to VBA array

[litrelord]

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:

    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

 

[dk]

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:-

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.

 

[litrelord]

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

 

[P Sitaram]

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

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

 

[litrelord]

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

 

[nisht]

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

 

[P Sitaram]

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

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

 

[neodjandre]

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