VBA to Filter on variable column
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 |
VBA to Filter on variable column
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. 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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
VBA to Filter on variable column
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. In article , Claus Busch writes 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. 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. |
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 |
VBA to Filter on variable column
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)) & "*") 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. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
VBA to Filter on variable column
Hi Claus OK that's perfect. Thanks for your time and considerable expertise. Best Wishes Colin In article , Claus Busch writes 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)) & "*") 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. |
All times are GMT +1. The time now is 01:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com