VBA to Filter on variable column

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

Colin Hayes

Hi

I need some help with some code, please.

It's to filter on an input column , and then to filter on input content.

So , It would be like this :

Run VBA

Reset any existing filters

Message box - 'Filter on which column?'

- input by user

Message box - 'Filter by which content'

- input by user

End


The column would then show those rows corresponding to the user input in
the selected column.

Can someone help with this? Grateful for any assistance.


Best Wishes
 
Hi Colin,

Am Mon, 26 May 2014 21:16:52 +0100 schrieb Colin Hayes:
The column would then show those rows corresponding to the user input in
the selected column.
Click to expand...

here is a suggestion with only one inputbox to enter column letter and
filter value comma separated:

Sub myFilter()
Dim myStr As String
Dim myArr As Variant

With ActiveSheet
.AutoFilterMode = False
myStr = Application.InputBox("Enter the column letter" _
& "and the filter value comma separated", _
"Column and Value Choice", Type:=2)
If myStr = "" Or myStr = "False" Then Exit Sub

myArr = Split(myStr, ",")
.UsedRange.AutoFilter field:=Columns(myArr(0)).Column, _
Criteria1:=Trim(myArr(1))
End With

End Sub


Regards
Claus B.
 
Hi Claus

Excellent - thank you. It works first time perfectly. I'm grateful.

BTW - would be an easy thing for the filter to show all rows containing
the input value rather than a strict literal match?

So , for example the input filter of "LP" would return rows with

LP
2LP
3LP

rather then just LP.

Thanks Claus.
 
Hi Colin,

Am Tue, 27 May 2014 14:44:53 +0100 schrieb Colin Hayes:
BTW - would be an easy thing for the filter to show all rows containing
the input value rather than a strict literal match?
Click to expand...
if you do that in all cases you can't filter for exact values.
So I changed the code that you can enter a third value into the
inputbox. Is the value 0 then will be filtered for the exact value, is
the value 1 will be filterde for substring.

If your "LP" values are in C then you can filter with
C,LP,0 for all "LP" exactlyor with C,1LP,0 fpr all "1LP" exactly
With
C,LP,1
you will get all LP values like 1LP, 2LP, LP and so on:

Sub myFilter()
Dim myStr As String
Dim myArr As Variant

With ActiveSheet
.AutoFilterMode = False
myStr = Application.InputBox("Enter the column letter," _
& "and the filter value comma separated", _
"Column and Value Choice", Type:=2)
If myStr = "" Or myStr = "False" Then Exit Sub

myArr = Split(myStr, ",")
.UsedRange.AutoFilter Field:=Columns(myArr(0)).Column, _
Criteria1:=IIf(myArr(2) = 0, Trim(myArr(1)), _
"*" & Trim(myArr(1)) & "*")
End With

End Sub

If you need another suggestion, please post again.


Regards
Claus B.
 
Hi again,

Am Tue, 27 May 2014 16:14:24 +0200 schrieb Claus Busch:
.UsedRange.AutoFilter Field:=Columns(myArr(0)).Column, _
Criteria1:=IIf(myArr(2) = 0, Trim(myArr(1)), _
"*" & Trim(myArr(1)) & "*")
Click to expand...

better change the lines above to:

.UsedRange.AutoFilter Field:=Columns(myArr(0)).Column, _
Criteria1:=IIf(UBound(myArr) = 1, Trim(myArr(1)), _
"*" & Trim(myArr(1)) & "*")

If you want filter for the exact value only enter the column letter and
the value. If you want filter for substring enter the third value e.g. 0
For exact value
C,LP
for substring
C,LP,0


Regards
Claus B.
 
Hi Claus

OK that's perfect.

Thanks for your time and considerable expertise.



Best Wishes


Colin
 
Back
Top