VBA Code to autofilter data using column name

[aman08u]

Hi,

I am trying to use below code to autofilter the data in an Excel sheet. But, I have 2 columns with same name i-e 'Labels' in an excel.
When I use below code, it always refer the first occurrence of the 'Label' column and ignore the other one.
Can anyone help me here please? I am new to VBA so not able to figure out the correct way.

Many thanks in advance!!!!!


Dim p As Integer, rngData7 As Range
Set rngData7 = Range("A1").CurrentRegion
p = Application.WorksheetFunction.Match("Labels", Range("A1:ZA1"), 0)
rngData7.AutoFilter p, Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "="), xlFilterValues

Dim p1 As Integer, rngData77 As Range
Set rngData77 = Range("A1").CurrentRegion
p1 = Application.WorksheetFunction.Match("Labels", Range("A1:ZA1"), 0)
rngData77.AutoFilter p1, Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "="), xlFilterValues

 

[DanteAmor]

Try this.

The first search is made towards the next, the second search is made to the previous one

Sub FilterColumns()
  Dim p As Integer
  Dim rngData7 As Range
  Dim f As Range
  Dim ary As Variant
  
  ary = Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "=")
  Set rngData7 = Range("A1").CurrentRegion
  Set f = Range("A1:ZA1").Find("Labels", , xlValues, xlWhole, , xlNext, False)
  If Not f Is Nothing Then
    p = f.Column
    rngData7.AutoFilter p, Array(ary), xlFilterValues
  End If
  
  Set f = Range("A1:ZA1").Find("Labels", , xlValues, xlWhole, , xlPrevious, False)
  If Not f Is Nothing Then
    p = f.Column
    rngData7.AutoFilter p, Array(ary), xlFilterValues
  End If
End Sub

 

[aman08u]

Thanks @DanteAmor for the quick turnaround. This is working fine for 2 columns with same name. But suppose if I have 3 columns with same name, then what needs to change here.

Try this.

The first search is made towards the next, the second search is made to the previous one

Sub FilterColumns()
  Dim p As Integer
  Dim rngData7 As Range
  Dim f As Range
  Dim ary As Variant
 
  ary = Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "=")
  Set rngData7 = Range("A1").CurrentRegion
  Set f = Range("A1:ZA1").Find("Labels", , xlValues, xlWhole, , xlNext, False)
  If Not f Is Nothing Then
    p = f.Column
    rngData7.AutoFilter p, Array(ary), xlFilterValues
  End If
 
  Set f = Range("A1:ZA1").Find("Labels", , xlValues, xlWhole, , xlPrevious, False)
  If Not f Is Nothing Then
    p = f.Column
    rngData7.AutoFilter p, Array(ary), xlFilterValues
  End If
End Sub

 

[DanteAmor]

I have 2 columns with same name

That was not in your initial question. It would be necessary to make another design to find more than 2 columns.
You really have a scenario with more than 2 columns. It would be more convenient if each column has its own name "Labels1", "Labels2", "Labels3" ... Even for a pivot table, for example, you need different names.

 

[aman08u]

That was not in your initial question. It would be necessary to make another design to find more than 2 columns.
You really have a scenario with more than 2 columns. It would be more convenient if each column has its own name "Labels1", "Labels2", "Labels3" ... Even for a pivot table, for example, you need different names.

Actually I am fetching data from JIRA. So in my jira project we have one text box called Labels. If Labels field have 2 or 3 values, then while downloading the data into excel...it will automatically creates 2 or 3 Labels column.

 

[DanteAmor]

For multiple columns, try this:

Sub FilterColumns()
  Dim rngData7 As Range, f As Range
  Dim ary As Variant
  Dim cell As String
  
  ary = Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "=")
  Set rngData7 = Range("A1").CurrentRegion
  Set f = rngData7.Rows(1).Find("Labels", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      rngData7.AutoFilter f.Column, Array(ary), xlFilterValues
      Set f = rngData7.Rows(1).FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

 

[aman08u]

For multiple columns, try this:

Sub FilterColumns()
  Dim rngData7 As Range, f As Range
  Dim ary As Variant
  Dim cell As String
 
  ary = Array("IMP", "BLB", "CTP", "KTLO", "REG", "MaintenanceRR", "OnTopOf", "Prior", "T1", "T2", "=")
  Set rngData7 = Range("A1").CurrentRegion
  Set f = rngData7.Rows(1).Find("Labels", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    cell = f.Address
    Do
      rngData7.AutoFilter f.Column, Array(ary), xlFilterValues
      Set f = rngData7.Rows(1).FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
  End If
End Sub

Cheers!!! Thank you so much. This is exactly what I was looking for. Appreciate your help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.