VBA to Filter on variable column
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?
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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|