vba filtering with multiple values

[KooKos]

Hi,

I am trying to filter pivot fields and datasets through user input with multiple filters. Pivot filters works fine in isolation. I have tried different variations and could not find a solution how to make them work together. Dataset can not be filtered with 3 criterias, and I could not figure out a way to get datasets filtered.

I have user input for stating a starting number, another user input for stating an ending number and one user input for stating additional numbers. For example starting value could be 100, ending value 199 and additional numbers 255 & 265. Macro contains several subs with multiple workbooks containing pivots and datafields. I will add below the relevant parts of the macro.

Public SalesStart As Integer
Public SalesEnd As Integer
Public SalesAdd As Integer

SalesStart = Application.InputBox(Prompt:="Enter first number of sales", Type:=1)
SalesEnd  = Application.InputBox(Prompt:="Enter last number of sales", Type:=1)
SalesAdd = Application.InputBox(Prompt:="Enter the numbers you want to filter" & vbNewLine & "Seperated by a comma (,)")


Public Function IsInArray(stringToBeFound As Long, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False
End Function


Dim Korrews As Worksheet
Dim arr() As String
Dim pi As PivotItem
Set Korrews = Sheets("Correlations")


Korrews.PivotTables("SalesCorrelations").AllowMultipleFilters = True


    If SalesStart <> False Then
    
    'This works on its own. Was not able to make them work together
    Korrews.PivotTables("SalesCorrelations").PivotFields("Number").PivotFilters.Add2 _
        Type:=xlCaptionIsBetween, Value1:=SalesStart, Value2:=SalesEnd


        If SalesAdd <> False Then

        'This works on its own. Was not able to make them work together
        SalesAdd = Trim(Replace(SalesAdd, " ", vbNullString))
                arr = Split(SalesAdd, ",")
        With Korrews.PivotTables("SalesCorrelations").PivotFields("Number")
            For Each pi In .PivotItems
                If Not SalesAdd = vbNullString Then
                    pi.Visible = IIf(IsInArray(pi.Name, arr), True, False)
                Else
                    pi.Visible = True
                End If
            Next pi
        End With


        Else


        End If
    Else


    Korrews.PivotTables("SalesCorrelations").PivotFields("Number").PivotFilters.Add2 _
        Type:=xlCaptionIsBetween, Value1:="100", Value2:="199"


    End If




'Tried filtering datasets by looping the filter field and hiding entirerow if value is not SalesAdd value. Just ended up crashing Excel multiple times. Below is the part that does work. Would need to filter values between SalesStart and SalesEnd and add values of SalesAdd.
If SalesStart <> False Then
        
    ActiveSheet.Range("A:A").AutoFilter field:=1, Criteria1:=">=" & SalesStart _
        , Operator:=xlAnd, Criteria2:="<=" & SalesEnd

I do not have much of experience with vba, so would truly appreciate if someone could help me out with this one. Have spent lots of time trying to figure it out. I tried to find solutions to these problems, but didn't find any working solutions (or maybe just didn't understand how to incorporate them on my macro ) so I am not even sure it can be done. If that is the case, I'm sure there are some workarounds to overcome these problems.

 

[DanteAmor]

Try this

Sub test()
  Dim SalesStart As Variant, SalesEnd As Variant, SalesAdd As Variant
  Dim arr1 As Variant, i As Long, n As Long
  
  SalesStart = Application.InputBox(Prompt:="Enter first number of sales", Type:=1)
  SalesEnd = Application.InputBox(Prompt:="Enter last number of sales", Type:=1)
  SalesAdd = Application.InputBox(Prompt:="Enter the numbers you want to filter" & vbNewLine & "Seperated by a comma (,)")
  
[COLOR=#0000ff]  arr1 = Split(SalesAdd, ",")[/COLOR]
  n = UBound(arr1) + 1
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If (Range("A" & i) >= SalesStart And Range("A" & i) <= SalesEnd) Then
      ReDim Preserve arr1(n)
[COLOR=#0000ff]      arr1(n) = Range("A" & i)[/COLOR]
      n = n + 1
    End If
  Next
  ActiveSheet.Range("A:A").AutoFilter field:=1, [COLOR=#0000ff]Criteria1:=arr1[/COLOR], Operator:=xlFilterValues
End Sub

 

[KooKos]

Brilliant! Works like a charm. Thank you for your effort.

I tried again to make the pivotfield filtering work with multiple filters, but failed to make it work. Clearly I am not competent enough , but will keep on studying. Nevertheless would be awesome if someone is able to guide me with the pivotfield filtering. I am sure there are lots of experienced vba users in this forum whom this is easy task.

 

[DanteAmor]

I thought you had solved the pivot table filter.
I put the complete code.

Sub FilterData()
  Dim SalesStart As Variant, SalesEnd As Variant, SalesAdd As Variant
  Dim arr As Variant, i As Long, n As Long
  Dim Ko As Worksheet, pi As PivotItem, exists As Boolean
  Application.ScreenUpdating = False
  '
  SalesStart = Application.InputBox(Prompt:="Enter first number of sales", Type:=1)
  SalesEnd = Application.InputBox(Prompt:="Enter last number of sales", Type:=1)
  SalesAdd = Application.InputBox(Prompt:="Enter the numbers you want to filter" & vbNewLine & "Seperated by a comma (,)")
  '
  Set Ko = Sheets("Correlations")
  If Ko.AutoFilterMode Then Ko.AutoFilterMode = False
  'Filter pivot table
  arr = Split(SalesAdd, ",")
  n = UBound(arr) + 1
  With Ko.PivotTables("SalesCorrelations").PivotFields("Number")
    .ClearAllFilters
    'Validation
      exists = False
      For Each pi In .PivotItems
        For i = LBound(arr) To UBound(arr)
          If arr(i) = pi.Name Then exists = True
        Next i
        If Val(pi.Name) >= SalesStart And Val(pi.Name) <= SalesEnd Then exists = True
        If exists Then Exit For
      Next
    'End Validation
    If exists = False Then
      MsgBox "There are no numbers with those cirterios"
      Exit Sub
    End If
    For Each pi In .PivotItems
      exists = False
      If Val(pi.Name) >= SalesStart And Val(pi.Name) <= SalesEnd Then exists = True
      If exists = False Then
        For i = LBound(arr) To UBound(arr)
          If arr(i) = pi.Name Then exists = True
        Next i
      End If
      pi.Visible = exists
    Next pi
  End With
  'Filter Range
  For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If (Range("A" & i) >= SalesStart And Range("A" & i) <= SalesEnd) Then
      ReDim Preserve arr(n)
      arr(n) = Range("A" & i)
      n = n + 1
    End If
  Next
  ActiveSheet.Range("A1").AutoFilter field:=1, Criteria1:=arr, Operator:=xlFilterValues
End Sub

 

[KooKos]

Thank you! Have been pretty busy last few days and missed your quick reply. I might have been little confusing with my initial post. I managed to get the filters working separately, but could not get them to work together.

Now, thanks to you, they do work together. However, I noticed that it only adds the first value of SalesAdd. For example if I would like to filter values between 100 - 199 and add values of 255 & 265 (SalesStart = 100, SalesEnd = 199, SalesAdd = 255,256), it only filters 100-199 + 255. I recall that I managed to filter SalesAdd with multiple values separated with comma successfully with the version I initially posted, but that didn't seem to work either. ****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

successfully

<tbody style="outline: none;">
</tbody>

</body>

 

[DanteAmor]

Works for me.
Do you have numerical values ​​on the sheet?
Review your data that has only numerical values.
Check that there are no spaces in the cells.
Did you modify some of the macro?