Filtering based on input in listbox

Kristina96

New Member
Joined
Sep 30, 2019
Messages
31
Hello all,

I need help with the following macro please. I am trying to use a userform to allow a selection of criteria. Based on the selection a column in the excel sheet should be filtered.
So far this is my code:

VBA Code:
   Dim Ary2 As Variant
   Dim i2 As Long, j2 As Long
   
   With Me.ListBox2
      ReDim Ary2(1 To .ListCount)
      For i2 = 0 To .ListCount - 1
         If .Selected(i2) Then
            j2 = j2 + 1
            Ary2(j2) = .List(i2)
         End If
      Next i2
      Ary2(j2 + 1) = "Everywhere"
 
      
    If Ary2.Contains("Dropdown!B20:B24") Then
        Ary2(j2 + 2) = "Everywhere (Europe)"
    End If
    If Ary2.Contains("Dropdown!B25:B36") Then
        Ary2(j2 + 3) = "Everywhere (Asia)"
    End If
    If Ary2.Contains("Dropdown!B37:B47") Then
        Ary2(j2 + 4) = "Everywhere (Australia)"
    End If

   End With
   ActiveSheet.Range("AreaColumn").AutoFilter 1, Ary2, xlFilterValues
Firstly, it is filtering for all selected options in the listbox.
Secondly, it is supposed to add specific filtering criteria based on the input. When it contains an element in the dropdown sheet (in given cells) it is supposed to add the given options with everywhere(xx) to the filter.
Currently it is not working.

Can anyone help me with that?
Any help is very much appreciated!

Thank you in advance and best regards,
Kristina
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

CSmith

Well-known Member
Joined
Jan 13, 2020
Messages
686
Office Version
365, 2010, 2007
Platform
Windows, Mobile, Web
Needs attached to a change event?
 

Watch MrExcel Video

Forum statistics

Threads
1,099,046
Messages
5,466,236
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top