0

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
karips
  • 151
  • 1
  • 1
  • 11
  • Is LastCol defined? – Matts Mar 06 '17 at 00:45
  • Dim LastCol As Integer With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With – karips Mar 06 '17 at 00:46
  • 1
    It needs the letter for that syntax instead of integer. "A1:" & LastCol + 1 & "1" – Matts Mar 06 '17 at 00:49
  • Can you edit your post with the rest of the code? – Matts Mar 06 '17 at 00:56
  • 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 Mar 06 '17 at 01:03
  • This answer shows how to get the letter. http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter – Matts Mar 06 '17 at 01:05
  • Wouldn't `LastCol + 1` be 1 column to the right of your last column? – Mark Fitzgerald Mar 06 '17 at 03:38

1 Answers1

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
Slai
  • 22,144
  • 5
  • 45
  • 53