Macro to filter the same column regardless of changes

**Barking_Mad** · 07-16-2010, 07:05 AM

Hi,

I’ve recorded a macro to run a filter (as below) it and it works fine. The only problem I have is that sometimes a column will be added, or possibly removed from the sheet - meaning the filter won’t always work. Is there a way to make sure the filter always does what it does on the same column, regardless of any changes to the column's location?

Sub late()
'
' late Macro
'

'
    Range("J8:J11").Select
    Selection.AutoFilter
    ActiveSheet.Range("$B$8:$J$94").AutoFilter Field:=9, Criteria1:="=*late*", _
        Operator:=xlAnd
End Sub

**pike** · 07-16-2010, 07:30 AM

Hi Barking_Mad,
Possibly if you know the field title eg if the coulmn title is "Two"

x=application.worksheetfunction.MATCH("Two",A7:z7,0)
ActiveSheet.Range("$B$8:$J$94").AutoFilter Field:=x, Criteria1:="=*......

**Barking_Mad** · 07-16-2010, 07:46 AM

Hi Pike,

Thanks, but im having trouble getting the compiler to accept the code....im crap at VBA so please excuse..It's giving me a synstax error

x=application.worksheetfunction.MATCH("Two", A7:z7,0)

Additionally, should i delete

Range("J8:J11").Select
    Selection.AutoFilter

from the original code?

**pike** · 07-16-2010, 07:51 AM

Hi Barking_Mad
Could you attach the workbook?

**Barking_Mad** · 07-16-2010, 08:41 AM

Here you go, ive taken bits and pieces of sensitive info out, but the column i want to filter is the ANNOTATIONS one on the end of the sheet, namely anything with LATE in the column of cells..

**pike** · 07-16-2010, 08:00 PM

Dude try this...

Option Explicit
Sub tester()
   Dim xCount As Long
    xCount = Application.WorksheetFunction.Match("Annotations", Range("A8:z8"), 0)
    With Cells(8, xCount)
        ActiveSheet.Range("B8:" & Chr(xCount + 64) & "94").AutoFilter Field:=xCount - 1, Criteria1:="=*late*"
    End With
End Sub