CODEWITHSUNDEEP
excelvba
Souza Saulo
Souza Saulo

Reputation: 61

Find and filter last column with values

I need a code that will find the last column with values and filter that column by multiple criteria. Here’s what I’ve come up with so far.

Dim LastCol As Integer
With ActiveSheet
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).column
End With
With ActiveSheet
    AutoFilter Field:=1, Criteria1:=Array("Age*", "Weight*"), 
Operator:=xlFilterValues

Upvotes: 1

Views: 890

Answers (1)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

First, I would recommend that you declare your worksheet object. I turned your LastCol into a function.

Try this:

Option Explicit

Function lastCol(ByVal ws As Worksheet, Optional ByVal row As Variant = 1) As Long
    With ws
        lastCol = .Cells(row, .Columns.Count).End(xlToLeft).Column
    End With
End Function

Sub test()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    With ws.UsedRange
        .AutoFilter Field:=lastCol(ws), Criteria1:=Array("Age*", "Weight*"), Operator:=xlFilterValues
    End With

End Sub

Using ActiveSheet, ActiveCell, .Select, .Selection, .Activate, etc is rarely good for your code. In minimal cases it's useful, but the vast majority of the time it can be a debugging nightmare.

Upvotes: 1

Related Questions