CODEWITHSUNDEEP
excelvba
Lalaland
Lalaland

Reputation: 308

Look for Column and then filter

I have headers starting on A3 and I am trying to get this code to look for column "Team Manager" and then filter on a login name.

Here is my code but it is not working sadly.

Sub FilterNames()

    Dim col As String, cfind As Range

    col = "Team Manager"
    With ActiveSheet.Select
        With .Range("A3", .Cells(1, .Columns.Count).End(xlToLeft))
            Set cfind = .Find(what:=col, LookIn:=xlValues, lookat:=xlWhole)
            If Not cfind Is Nothing Then
                .AutoFilter Field:=cfind.Column, Criteria1:="login123" '                   
            End If
        End With
        .AutoFilterMode = False
    End With

End Sub

Upvotes: 1

Views: 78

Answers (2)

user3259118
user3259118

Reputation:

It's an interesting little problem. Please try the following code, based on all your headers being in row 3, on the sheet called Sheet1 - change these to suit.

Option Explicit
Sub FilterNames()
Dim ws As Worksheet
Dim LastRow As Long, col As Long

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

Set ws = Sheets("Sheet1")
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

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33692

Your error is in this line:

With .Range("A3", .Cells(1, .Columns.Count).End(xlToLeft))

you are looking for last column in your Filtered headers, which is in your 3rd row, so it needs to be:

With .Range("A3", .Cells(3, .Columns.Count).End(xlToLeft))

Also, try to avoid using ActiveSheet and Select.

Modified Code

Option Explicit

Sub FilterNames()

    Dim col As String, cfind As Range
    Dim Sht As Worksheet
    
    Set Sht = ThisWorkbook.Worksheets("Sheet1")  ' <-- modify "Sheet1" to your sheet's name
    col = "Team Manager"
    With Sht
        With .Range("A3", .Cells(3, .Columns.Count).End(xlToLeft))
            Set cfind = .Find(what:=col, LookIn:=xlValues, lookat:=xlWhole)
            If Not cfind Is Nothing Then
                .AutoFilter Field:=cfind.Column, Criteria1:="login123" '
            End If
        End With
        .AutoFilterMode = False
    End With

End Sub

Upvotes: 0

Related Questions