Consulting

Results 1 to 3 of 3

Thread: Array as variable

  1. #1
    VBAX Contributor
    Joined
    Nov 2009
    Posts
    114
    Location

    Array as variable

    Dear all,

    how to set the array as variable

     ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Array(Sheets("Summary").Cells(1, 1).Value), Operator:=xlFilterValues
    Cells(1, 1).Value = "10", "11", "12","1", "2", "3"

  2. #2
    Moderator VBAX Master georgiboy's Avatar
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    1,158
    Location
    If your values in Cells(1,1) have the "" then maybe:
    ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Replace(Sheets("Summary").Cells(1, 1).Value, """", ""), ", "), Operator:=xlFilterValues
    Else:
    ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=Split(Sheets("Summary").Cells(1, 1).Value, ", "), Operator:=xlFilterValues
    Click here for a guide on how to add code tags
    Click here for a guide on how to mark a thread as solved

    Excel 365, Version 2401, Build 17231.20084

  3. #3
    you can also create a udf and use it in your filtering:
    Private Sub t()
     ActiveSheet.Range("$A$1:$M$20000").AutoFilter Field:=3, Criteria1:=fnRangeToArray(Sheets("Summary").Cells(1, 1)), Operator:=xlFilterValues
    End Sub
    
    
    'arnelgp
    Public Function fnRangeToArray(rng As Range, Optional ByVal delimeter As String = ",") As Variant
    
    
        Dim ret() As String
        Dim arr As Variant
        Dim c As New Collection
        Dim i As Integer, j As Integer
        Dim s As String
        
        arr = Split(rng.Value, delimeter)
        
        For i = 0 To UBound(arr)
            s = Trim$(Replace$(arr(i) & "", """", ""))
             'Debug.Print s
            If Len(s) Then
                j = j + 1
                c.Add Item:=s, Key:=j & ""
            End If
        Next
        
        If c.Count <> 0 Then
            ReDim ret(c.Count - 1)
            For i = 1 To c.Count
                ret(i - 1) = c(i & "")
            Next
        End If
        
        fnRangeToArray = ret
        
    End Function

Posting Permissions

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