1

I have a table with 15 columns (A:O).In 12th column (L) which contain 200 countries. I want to remove (Making invisible) more than 1 countries from that filter using InputBox. I used the code below and it is not working. It is showing error "Type mismatch".

If i am not using <> , then it is selecting only user entered data. But i want to deselect the user entered data. How to use "not equal to" inside an Array. Help me

Sub Removecountries()

Dim str1 As Variant
Dim Data As Variant

str1 = Application.InputBox("Enter the Country - comma separated ")

Sheet2.ListObjects("DataTable").Range.AutoFilter Field:=12

If InStr(1, str1, ",") > 0 Then
Data = Split(str1, ",")
For i = LBound(Data) To UBound(Data)

Sheet2.ListObjects("DataTable").Range.AutoFilter Field:=12, Criteria1:=Array("<>" & Data), Operator:=xlFilterValues 'error

Next i
Else

Sheet2.ListObjects("DataTable").Range.AutoFilter Field:=12, Criteria1:=("<>" & str1), Operator:=xlFilterValues

End If
End Sub
Deepak
  • 473
  • 1
  • 10
  • 32
  • in Criteria1:=Array("<>" & Data) you are trying to pass an array (Data is the result of Split(), which returns an array of strings).. This results in a type mismatch... try passing Data(i), which should be a string. – Wimpel Dec 06 '17 at 10:37
  • @Jeeped Your answers shows "How to make it visible". But i need to make invisible (Hide) what user enters – Deepak Dec 06 '17 at 10:45
  • @Wimpel I already tried. Its not working – Deepak Dec 06 '17 at 10:47
  • you need to build a dictionary of keys for every country that should be shown (i.e. **not** on your list) and use that as the criteria. That is **exactly** what my linked answer does. Go back and read it again. –  Dec 06 '17 at 10:52
  • You need to create an array which won't have the excluded countries and pass it to array: `Criteria1:=ArrayWithExcludedCountries, Operator:=xlFilterValues` – JohnyL Dec 06 '17 at 10:53

0 Answers0