Excel VBA Help, Filters

[Chris_010101]

Hello,

In column A, I have:

=SORT(UNIQUE(FILTER('Headcount Report'!A3:A909, 'Headcount Report'!A3:A909<>"")))

which pulls a list of unique values. In this list are locations by type, i.e St Albans - Yard, Colchester - Transport. The three types are "Office", "Yard" and "Transport" all appended with a hyphen a the end of the location.

In corresponding columns are headcount, vacancies and other information. I have created macro buttons to filter column A by the three types mentioned above. However, if I expand the list, say we add a new location, it doesn't come through in the filter. I have to re-record the macro, which is annoying.

Example:

Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:=Array( _
        "Belfast - Office", "Bristol - Office", "Chester - Office", "Colchester - Office", _
        "Newbury - Office", "Peterlee - Office", "Rochford - Office", "Sandtoft - Office", _
        "Sandwich - Office", "Sandy - Office", "Westbury - Office", "Whitburn - Office", _
        "Wisbech - Office", "Wolverhampton - Office", "York - Office"), Operator:= _
        xlFilterValues
    ActiveWindow.SmallScroll Down:=-6
End Sub

This doesn't update when the filtered list in column A is changed. For example, if I was now to add "St Albans - Office" to the array, the macro would not pick it up unless I re-record it.

Kind Regards

 

[Fluff]

How about

Sub Office()
'
' Office Macro
'

'
    ActiveSheet.Range("$A$1:$D$46").AutoFilter Field:=1, Criteria1:="*Office*"
    ActiveWindow.SmallScroll Down:=-6
End Sub

 

[Chris_010101]

Thanks very much

 

[Fluff]

You're welcome & thanks for the feedback.