Filter/soft macro - make it dynamic

[EdwardSurrey]

Hello

I have the following simple Macro that filters a list in Excel. It's filtering Column 3 (Names) by the criteria "B". The issue is, I plan to add new columns sometimes, and this will change the location of the field.

Is there a way to make it dynamic?

Sub Filter3()
'
' Filter3 Macro
'

'
ActiveSheet.Range("$B$7:$H$11").AutoFilter Field:=3, Criteria1:="B"
End Sub

Thanks!

 

[HaHoBe]

Hi EdwardSurrey,

maybe like this: assign a name to the header cell named Names (I used myName for demonstration) and alter your code to

Sub Filter3()
'
' Filter3 Macro
'

'
'As your data starts in Column B yiou need to correct the column letter by - 1
ActiveSheet.Range("$B$7:$H$11").AutoFilter Field:=Range("myName").Column - 1, Criteria1:="B"
End Sub

Ciao,
Holger

 

[EdwardSurrey]

Hi EdwardSurrey,

maybe like this: assign a name to the header cell named Names (I used myName for demonstration) and alter your code to

Sub Filter3()
'
' Filter3 Macro
'

'
'As your data starts in Column B yiou need to correct the column letter by - 1
ActiveSheet.Range("$B$7:$H$11").AutoFilter Field:=Range("myName").Column - 1, Criteria1:="B"
End Sub

Ciao,
Holger

Perfect! Thank you