Find Column and Filter by text-CodePudding

I have the below code which looks at the current open sheet, looks for column Team Manager and filters text. The headers are on the 3rd row and the column Team Manager might change to TM hence me using wild card.

For some reason, it is not working. Am i missing something?

Here is my code

Option Explicit

Sub FindMatt()
Dim ws As Worksheet
Dim LastRow As Long, col As Long

Const login = "matroux"
Const header = "T*M*"

Set ws = ActiveSheet
col = Application.WorksheetFunction.Match(header, ws.Range("3:3"), 0)
LastRow = ws.Cells(Rows.Count, col).End(xlUp).Row

With ws.Range(ws.Cells(3, col), ws.Cells(LastRow, col))
    .AutoFilter 1, login
End With

End Sub

CodePudding user response：

AutoFilter Data

I'm not sure why your code didn't work (but works now; my guess would be you had another filter active) but the following illustrates what could go wrong. Also, you need to make sure the correct worksheet is active when using ActiveSheet.

Sub FindMatt()
    
    Const Login As String = "matroux"
    Const Header As String = "T*M*"
    Const HeaderRow As Long = 3
    
    Dim ws As Worksheet: Set ws = ActiveSheet
    If ws.FilterMode Then ws.ShowAllData
    
    Dim Col As Variant ' it could be an error value hence 'As Variant'
    Col = Application.Match(Header, ws.Rows(HeaderRow), 0)
    
    If IsError(Col) Then ' this doesn't work with 'WorksheetFunction.Match'
        MsgBox "Header not found.", vbCritical
        Exit Sub
    End If
    
    Dim LastRow As Long: LastRow = ws.Cells(ws.Rows.Count, Col).End(xlUp).Row
    
    If LastRow <= HeaderRow Then
        MsgBox "No data in column range.", vbCritical
        Exit Sub
    End If
    
    With ws.Range(ws.Cells(HeaderRow, Col), ws.Cells(LastRow, Col))
        .AutoFilter 1, Login
    End With

End Sub