1

I have a question, hopefully you may be able to help. I have been using below code that just filter the single String from the given s.Range("C11:C2008").

when i have added 2nd string it stop working textb_deger = "Define Problem" & "Keep" it stop working.

Then i added a range with multiple strings like textb_deger = Sheet2.Range("A20:A40") it again stop working.

I have been trying to add a number of strings by range i.e. (Sheet2.Range("A20:A40")) and when i run the code those string should be filtered which are available in the given range.

Your help will be greatly appreciated.

Sub datcode()
    Dim s As Worksheet, textb_deger As r
    Set s = Sheet7
    textb_deger = "Define Problem"
    s.AutoFilterMode = False
    s.Range("C11:C2008").AutoFilter
    s.Range("C11:C2008").AutoFilter field:=1, Criteria1:="=*" & textb_deger & "*"
End Sub
  • `textb_deger` is defined as `r` which is not a [standard type of variable](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/data-type-summary). Can you post the `r` class module? Also when you set `textb_deger = "Define Problem" & "Keep"` the result will be `Define ProblemKeep`, with no space between. Is that intentional? – Evil Blue Monkey Feb 26 '21 at 12:43
  • Oh thanks i got your point but it was not intentional. I am not an expert so thought to get help from this forum. –  Feb 26 '21 at 12:51

1 Answers1

2

If you want to filter multiple values use an array:

Range("$A$1").AutoFilter Field:=1, Criteria1:=Array("string A", "string B", "etc etc"),
_Operator:= xlFilterValues

You could also construct an array from cell values e.g. :

Sub CellsToArray()

Dim x As Integer
Dim myArray(2) As Variant

For x = 0 To 2
    myArray(x) = Cells(x + 1, 1).Value ' row x + 1, column 1
Next x

Range("$A$1").AutoFilter Field:=1, Criteria1:=Array(myArray), Operator:=xlFilterValues
    
End Sub

There are very many ways to do the same as the sub above, a discussion of all methods would be beyond the scope of this question but you'll find many examples here on SO and elsewhere.

Absinthe
  • 3,258
  • 6
  • 31
  • 70
  • Thank you for the help. But i have been looking for criteria by this Sheet2.Range("A20:A40"), Not by adding multiple strings. Problem is when that string will be changed in the sheets i have to added the strings again. –  Feb 26 '21 at 12:53
  • Build an array from the cell values. Read up on string arrays, plenty of tutorials online & many posts here e.g. https://stackoverflow.com/questions/7649046/one-dimensional-array-from-excel-range/7651439 – Absinthe Feb 26 '21 at 12:53
  • Thank you once again @Absinthe for the help. But i am not an expert who could use arrays with the code and redevelop it. I can just make minor changes to the code as i write one of my above. –  Feb 26 '21 at 13:06
  • 1
    You wanted column C, so that would be `cells(x, 3)`, as C is the third column. The array would be defined as `myArray(1996)` as row 2008 minus row 11 is 1997 and arrays start at zero rather than one. You'll also need to adjust the `for x = 0 to 2` and `cells(x + ` parts. It's not too difficult, you'll get the hang of it! I recommend reading some basic tutorials, e.g. https://www.tutorialspoint.com/vba/vba_arrays.htm – Absinthe Feb 26 '21 at 13:20
  • Thank you very much i will try my best to make this work. –  Feb 26 '21 at 13:57