excel vba macro to apply filter multiple column using header names

[soft_reg]

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]

Use Advanced Filter with your 3 column names in the Criteria range

[soft_reg]

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

[mana]

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]

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]

Here is the Advanced Filter Solution. Columns can be in any position.

[mana]

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]

You could use Inputbox for the occasional change

s(1, 2) = InputBox("Country", , "BEL")

[mana]

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]

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]

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]

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]

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

[mana]

If you need to change conditions,
it's recommended to adapt post #6.