Results 1 to 7 of 7

Filter list to VBA array

This is a discussion on Filter list to VBA array within the Excel Questions forums, part of the Question Forums category; Good morning, Hope somebody can help me with this or let me know if it's possible. I'd like to use ...

  1. #1
    Board Regular
    Join Date
    Dec 2002
    Posts
    519

    Default Filter list to VBA array

    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

  2. #2
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: Filter list to VBA array

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2002
    Posts
    519

    Default Re: Filter list to VBA array

    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

  4. #4
    Board Regular P Sitaram's Avatar
    Join Date
    Jun 2003
    Posts
    1,974

    Default Re: Filter list to VBA array

    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

  5. #5
    Board Regular
    Join Date
    Dec 2002
    Posts
    519

    Default Re: Filter list to VBA array

    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

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Ahmedabad Gujarat
    Posts
    303

    Default Re: Filter list to VBA array

    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
    ni■■■■h desai
    Mindful act is consecration only road to salvation.

  7. #7
    Board Regular P Sitaram's Avatar
    Join Date
    Jun 2003
    Posts
    1,974

    Default Re: Filter list to VBA array

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com