VBA doesn't seem to be recognizing my AutoFilter status

[CaraM]

I'm self-taught, and apparently not a very good teacher.

I have a workbook with multiple sheets that contains my tasks list. I have buttons that perform specific filters and sorts and, up until I tried a few days ago to resolve the problem I describe in another thread (https://www.mrexcel.com/forum/excel...multiple-columns-post5229747.html#post5229747), which I thought I had resolved, I was NOT having a problem with this.

But, suddenly, the code for those buttons doesn't seem to be recognizing my AutoFilter status.

I have commented out the code I added, and am trying to roll my code back to when it worked, but I'm still having issues. I think it may stem from my confusion over range and worksheet objects, but I'm not really sure.

I have global variables set to hold the column number for specifically-headed columns (you'll see 'colname' in my code below. That code also

Set wb = ThisWorkbookSet wsList = wb.Worksheets("List")

I click my "filter by age" button and run this:

Public Sub btnAgeRangeFilter_Click()
If Application.EnableEvents = False Then Application.EnableEvents = True
    If ColHeaderRow = 0 Then Call globalVars


    CurrRow = ActiveCell.Row
    CurrCol = ActiveCell.Column
    
    If wsList.AutoFilterMode Then
        ColHeaderRow = ColHeaderRow
    Else: Cells(ColHeaderRow, 1).AutoFilter
    End If
    
    Dim oldest As Integer
    Dim newest As Integer
    Dim stdt As Date
    Dim enddt As Date
    
    oldest = InputBox("Age in days of the oldest to see", "Range of age (startdate) of tasks to see", 1)
    newest = InputBox("Age in days of the newest to see", "Range of age (startdate) of tasks to see", 1)
    
    stdt = DateAdd("d", 0 - oldest, Date)
    enddt = DateAdd("d", 0 - newest, Date)
  
    With Cells(ColHeaderRow, 1)
        .AutoFilter field:=ColStartDate, Criteria1:=">=" & stdt, Criteria2:="<=" & enddt
        .AutoFilter field:=ColComplete, Criteria1:="<>C*"


    End With
 
    Application.EnableEvents = False
    Cells(ColHeaderRow, ColTask).Value = "Filter = between " & stdt & " and " & enddt & " and not complete/canceled" & Chr(10) & "Tasks"
    Cells(ColHeaderRow, CurrCol).Activate
    ActiveCell.Offset(1).Select
    Application.EnableEvents = True
End Sub

I'm left with filter buttons on my column header row.

Then I click my "Sort by Age" button to run this:

Private Sub btnSortAge_Click()
    If ColHeaderRow = 0 Then Call globalVars
    Dim vLineCount As Integer
    Dim vTaskHdr As Variant
    Dim vTaskHdrLen As Integer
    
    If wsList.AutoFilterMode Then
        ColHeaderRow = ColHeaderRow
    Else: Cells(ColHeaderRow, 1).AutoFilter
    End If


    CurrRow = ActiveCell.Row
    CurrCol = ActiveCell.Column


    With wsList.AutoFilter.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Columns(ColAge), Order:=xlDescending
        .SortFields.Add Key:=Columns(ColStartTime), Order:=xlAscending
        .SortFields.Add Key:=Columns(ColGoal), Order:=xlAscending
        .SortFields.Add Key:=Columns(ColWhen), Order:=xlAscending
        .SortFields.Add Key:=Columns(ColPriority), Order:=xlAscending
        .Header = xlYes
        '.Apply
    End With


    vTaskHdr = Cells(ColHeaderRow, ColTask).Value
    vTaskHdrLen = Len(vTaskHdr)
    vLineCount = vTaskHdrLen - Len(Replace(vTaskHdr, Chr(10), "")) + 1
    
    Select Case vLineCount
        Case 1: Cells(ColHeaderRow, ColTask).Value = "Sorted by Age" & Chr(10) & "Tasks"
    End Select


    Cells(CurrRow, CurrCol).Activate
    ActiveCell.Offset(1).Select
End Sub

(The Apply in my sort is commented out because it worked a few days ago but now gives an error - I can live without it if the sort is applied, which it seems to be.)

As I said, after clicking the button that filters by age, I have drop-downs indicating a filter in my column header row. But, when I click the Sort button, and debug / step through my code, the wsList.AutofilterMode property is FALSE. But I'm looking at the drop-downs. Is the presence of the drop-downs on my column headers not an indication that AutoFilter is ON?

 

[jjasmith4]

Are you sure wsList is active when the code runs? In general you should never write Range("A1") or Cells(1,1) alone, because those depend on the active sheet in the active workbook, which may change. You should always write wsList.Range("A1") or wsList.Cells(1,1) to be specific.


Next, you should apply the AutoFilter method to the whole column of data, not just the header, so that Excel will gather up all the unique items and know what range to sort. And the best way to do that is:

If wsList.AutoFilterMode Then wsList.AutoFilterMode = False ' Make sure the arrows are off
wsList.Cells(ColHeaderRow).Resize(TotalNumberOfRows).AutoFilter ' Now you'll have one arrow

...where you'll have to figure out what TotalNumberOfRows may be.


Finally, why are you sorting the AutoFilter? Normally you sort the range, namely wsList.Cells(ColHeaderRow).Resize(TotalNumberOfRows), as above.


Hope this helps!

 

[CaraM]

I'll give your example a try. In the meantime, I removed all attempts to check the status of the autofilter and everything seems to work fine.

But I know I don't really understand what I'm doing, so I will consider implementing your ideas. I also fear that, when I try to add back in the code I'm trying to build to do an AdvancedFilter off another button, it will screw me up again if I don't fix it.

Thanks!