Using variable as a column for autofilter (vba)

Question

I'd like to use a variable as a column for autofilter in vba, I think I'm not getting the sintax right, cause it`s not working:

With ActiveSheet
.AutoFilterMode = False
With .Range("A1:" & LastCol + 1 & "1")
.AutoFilter
.AutoFilter field:=5, Criteria1:="Approved"
.AutoFilter field:=6, Criteria1:="Open"
.AutoFilter field:=LastCol + 1, Criteria1:="1"
End With
End With

Dim LastCol As Integer With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With — karips
– karips, Commented Mar 6, 2017 at 0:46
It needs the letter for that syntax instead of integer. "A1:" & LastCol + 1 & "1" — Matts
– Matts, Commented Mar 6, 2017 at 0:49
actually, is that a way of getting the letter for the last column? all I have is the number (because the last column can change depending on the sheet. or.. is it a way of substituting the sintax for Range in the AutoFilter sub for a numbered column? — karips
– karips, Commented Mar 6, 2017 at 1:03

Slai · Accepted Answer · 2017-03-06 01:07:42Z

1

If there are no blank columns, you can use the CurrentRegion instead (similar to a Ctrl + A in A1)

ActiveSheet.AutoFilterMode = False
With Cells.CurrentRegion
    .AutoFilter 5, "Approved"
    .AutoFilter 6, "Open"
    .AutoFilter .Columns.Count, "1"
End With

answered Mar 6, 2017 at 1:07

Slai

23k5 gold badges49 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using variable as a column for autofilter (vba)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related