locked
Autofilter Refresh RRS feed

  • Question

  • Hey, does anybody know why my autofilter is not working when ActiveSheet is either 2 or 3? It works only when I have activated Sheet1... I want to have a code, that after filtering the data and using it to plot a chart, I could again filter the data using different criteria and again plot chart... I need that after using Autofilter and plotting data which I need, Autofilter would be again deactivated for using it again.

    Any suggestions and corrections of my code?

    This is my code:

    Sub AutoFiler()
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim starttime As Date
        Dim starttimestr As String
        Dim endtime As Date
        Dim endtimestr As String
        Dim TimeCells As Range
        Dim ValueCells As Range
        Dim stime As String
        Dim etime As String
       
        stime = InputBox("Enter Start Time")
        etime = InputBox("Enter End Time")
       
        With Sheets("Sheet1")
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If

            '.Columns("B:B").NumberFormat = "dd-mm-yyyy hh:mm"
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            starttime = DateValue(stime) + TimeValue(stime)
            starttimestr = Format(starttime, "m/d/yyyy hh:mm")
            endtime = DateValue(etime) + TimeValue(etime)
            endtimestr = Format(endtime, "m/d/yyyy hh:mm")

            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr

    Wednesday, December 17, 2014 1:54 PM

Answers

  • Hi GyTasS,
     
    Because you did not include the dots here:
     
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If
           
    That should read:
     
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Marked as answer by GyTasS Wednesday, December 17, 2014 4:50 PM
    Wednesday, December 17, 2014 3:38 PM

All replies

  • Hi GyTasS,
     
    Because you did not include the dots here:
     
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If
           
    That should read:
     
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    • Marked as answer by GyTasS Wednesday, December 17, 2014 4:50 PM
    Wednesday, December 17, 2014 3:38 PM
  • Thanks! Perfect! Now it works!
    Wednesday, December 17, 2014 4:50 PM