View Full Version : excel vba macro to apply filter multiple column using header names
soft_reg
08-12-2017, 12:50 PM
Hi Guys
Good day
I'm new to VBA programming. I would like to apply filter on three columns with header name because I've a sheet with more than 100 columns and the position of the three columns changes everyday.
SearchCol1= Country; SearchFor1= "BEL"
SearchCol2= Circuit_type; SearchFor2= "NEW_CIRCUIT"
SearchCol3= Business_Opportunity; SearchFor3= "Vendor Managed Services"
I'm able to use find to apply search on one column.
Sub ABEL()
SearchCol1 = "Country"
SearchFor1 = "BEL"
Dim rng1 As Range
Set rng1 = ActiveSheet.UsedRange.Find(SearchCol1, , xlValues, xlWhole)
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=rng1.Column, Criteria1:=SearchFor1
End Sub
Any help will be much appreciated.
Regards
mdmackillop
08-12-2017, 03:23 PM
Use Advanced Filter with your 3 column names in the Criteria range
soft_reg
08-13-2017, 02:18 AM
Use Advanced Filter with your 3 column names in the Criteria range
Hi mdmackillop
thanks for your response.
I need a macro besides the column name doesn't remain in the same field. I mean for example today I receive a file where header "Country" would be in column G (cell G1) and tomorrow I would receive a file for the same with header "Country" in Column P (cell P1). It's the same with other two headers.
Thanks for helping me.
Regards
Option Explicit
Sub test()
Dim s(1 To 3) As String
Dim ret As String
Dim m, i As Long
s(1) = "Country"
s(2) = "Circuit_type"
s(3) = "Business_Opportunity"
With Range("A1").CurrentRegion
.AutoFilter
For i = 1 To 3
ret = InputBox(s(i) & "?")
If ret <> "" Then
m = Application.Match(s(i), .Rows(1), 0)
.AutoFilter m, ret
End If
Next
End With
End Sub
soft_reg
08-13-2017, 04:19 AM
Hi Mana
Good day and many thanks for your response. It's working perfect.
One more question, I need to hard code the values to the below because this is my domain, it remains the same.
Country >>>>>>>>>>>>>> BEL
Circuit_type >>>>>>>>>>> NEW_CIRCUIT
Business_Opportunity >>>> Vendor Managed Services
Can I use an array where I can just replace the value in case my designated country changes from BEL to HOL which happens rare.
Thanks a million for your help.
Regards
Option Explicit
Sub test()
Dim s(1 To 3) As String
Dim ret As String
Dim m, i As Long
s(1) = "Country"
s(2) = "Circuit_type"
s(3) = "Business_Opportunity"
With Range("A1").CurrentRegion
.AutoFilter
For i = 1 To 3
ret = InputBox(s(i) & "?")
If ret <> "" Then
m = Application.Match(s(i), .Rows(1), 0)
.AutoFilter m, ret
End If
Next
End With
End Sub
mdmackillop
08-13-2017, 04:24 AM
Here is the Advanced Filter Solution. Columns can be in any position.
Option Explicit
Sub test2()
Dim s(1 To 3, 1 To 2) As String
Dim m, i As Long
s(1, 1) = "Country"
s(1, 2) = "BEL"
s(2, 1) = "Circuit_type"
s(2, 2) = "NEW_CIRCUIT"
s(3, 1) = "Business_Opportunity"
s(3, 2) = "Vendor Managed Services"
With Range("A1").CurrentRegion
.AutoFilter
For i = 1 To 3
m = Application.Match(s(i, 1), .Rows(1), 0)
.AutoFilter m, s(i, 2)
Next
End With
End Sub
mdmackillop
08-13-2017, 05:02 AM
You could use Inputbox for the occasional change
s(1, 2) = InputBox("Country", , "BEL")
advancedfilter
Sub test3()
Dim r As Range
Dim c As Range
Set r = Range("A1").CurrentRegion
Set c = r.Resize(2, 3).Offset(r.Rows.Count + 2)
c.Rows(1).Value = [{"Country","Circuit_type","Business_Opportunity"}]
c.Rows(2).Value = [{"BEL","NEW_CIRCUIT","Vendor Managed Services"}]
r.AdvancedFilter xlFilterInPlace, c
c.ClearContents
End Sub
soft_reg
08-13-2017, 05:10 AM
Here is the Advanced Filter Solution. Columns can be in any position.
Hi mdmackillop
Good day
Thanks a bunch for your response.
I'm unable to download the file, is there something missing that I need to do so that I be able to download the file.
each time it's downloading it as "attachment.php.html"
Thanks again for your efforts.
soft_reg
08-13-2017, 05:12 AM
Option Explicit
Sub test2()
Dim s(1 To 3, 1 To 2) As String
Dim m, i As Long
s(1, 1) = "Country"
s(1, 2) = "BEL"
s(2, 1) = "Circuit_type"
s(2, 2) = "NEW_CIRCUIT"
s(3, 1) = "Business_Opportunity"
s(3, 2) = "Vendor Managed Services"
With Range("A1").CurrentRegion
.AutoFilter
For i = 1 To 3
m = Application.Match(s(i, 1), .Rows(1), 0)
.AutoFilter m, s(i, 2)
Next
End With
End Sub
Hi Mana
Great. works perfect !!!
I'll try the below one as well. :) Thank you very much.
mdmackillop
08-13-2017, 05:17 AM
I'm unable to download the file, is there something missing that I need to do so that I be able to download the file.
I just Left Click and SaveAs
soft_reg
08-13-2017, 05:25 AM
advancedfilter
Sub test3()
Dim r As Range
Dim c As Range
Set r = Range("A1").CurrentRegion
Set c = r.Resize(2, 3).Offset(r.Rows.Count + 2)
c.Rows(1).Value = [{"Country","Circuit_type","Business_Opportunity"}]
c.Rows(2).Value = [{"BEL","NEW_CIRCUIT","Vendor Managed Services"}]
r.AdvancedFilter xlFilterInPlace, c
c.ClearContents
End Sub
WOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOW
This can be used for unlimited options .... just increase the size of the array and add any number of filters. May be I might need a couple more in future just that.
Curious to know what is the maximum number of filters that I can apply using the macro in excel 2010.
Really appreciate your help here. Grateful to you for your help.
Regards
If you need to change conditions,
it's recommended to adapt post #6.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.