VBA to search for multiple values

[burniksapwet]

Guys I need some help. I need a way to do a filter to where I can just type something like this and get the result 82CL10, 182CL21, 82CM10, 282CM29, 82CM32, 992CS12, 82DT152 and filter just these results. Is this at all possible? I have attached the excel file and a pdf of a sample of what I want it to be. Thank you in advance everyone.

https://www.dropbox.com/s/qnvypuyhhkv9v5z/Book1.xlsx?dl=0
https://www.dropbox.com/s/e4egroarqbms4vn/Book1.pdf?dl=0

I'm not sure if link I made can be viewed by everyone. Please let me know how it turns out. Thank you so much once again.

 

[Fluff]

If you type your list into cells J1, J2 etc try this

Sub FilterOnList()

   Dim Ary As Variant

   Ary = Application.Transpose(Range("J1", Range("J" & Rows.Count).End(xlUp)).Value)
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:F1").AutoFilter 4, Ary, xlFilterValues
   End With
   
End Sub

 

[burniksapwet]

If you type your list into cells J1, J2 etc try this

Sub FilterOnList()

   Dim Ary As Variant

   Ary = Application.Transpose(Range("J1", Range("J" & Rows.Count).End(xlUp)).Value)
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:F1").AutoFilter 4, Ary, xlFilterValues
   End With
   
End Sub

Thank you so much sir. The was quick and that was exactly what we need. I hope that you will be willing to help us out update this to a more flexible vba sometime in the future. I will explain the details at a later time. But for now this is exactly what we need. We love it. Thank you.

 

[Fluff]

Glad to help & thanks for the feedback

 

[burniksapwet]

Glad to help & thanks for the feedback

Hello again Fluff. Thank you for creating this so fast for us. We now have an additional request and hoping you could help out. We were wondering if there is now a way to select which tab we want the filter results to be at? We plan to list all the numbers in A1 or A2 but what we are filtering it too may not always be on the same column. Sometime we would want the filter to be in column B or C or even F or J. Is there a way for you to update the macro that would create a pop up dialog box telling us to select the column we want to filter. I hope this is something that is possible and we would like to thank you in advance. Thank you so much.

 

[Fluff]

Try

Sub FilterOnList()

   Dim Ary As Variant
   Dim Col As String
   
   Col = InputBox("Please enter a column number")
   If Len(Col) = 0 Then Exit Sub

   Ary = Application.Transpose(Range("J1", Range("J" & Rows.Count).End(xlUp)).Value)
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:F1").AutoFilter CLng(Col), Ary, xlFilterValues
   End With
   
End Sub

 

[burniksapwet]

Try

Sub FilterOnList()

   Dim Ary As Variant
   Dim Col As String
   
   Col = InputBox("Please enter a column number")
   If Len(Col) = 0 Then Exit Sub

   Ary = Application.Transpose(Range("J1", Range("J" & Rows.Count).End(xlUp)).Value)
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:F1").AutoFilter CLng(Col), Ary, xlFilterValues
   End With
   
End Sub

Great job sir. This is perfect. We were just wondering if there is a way to list the column letter instead of the number so that we don't have to count what column we're on. Is this even possible? Thank you once again for all the help. Our group really appreciates it.

 

[Fluff]

Will you ever be looking for a column after Z?

 

[burniksapwet]

No we will not

 

[Fluff]

Ok, how about

Sub FilterOnList()

   Dim Ary As Variant
   Dim Col As String
   
   Col = InputBox("Please enter a column number")
   If Len(Col) = 0 Then Exit Sub

   Ary = Application.Transpose(Range("J1", Range("J" & Rows.Count).End(xlUp)).Value)
   With ActiveSheet
      If .AutoFilterMode Then .AutoFilterMode = False
      .Range("A1:F1").AutoFilter Asc(UCase(Col)) - 64, Ary, xlFilterValues
   End With
   
End Sub