VBA Table filter and refresh

1
votes

Manual choiceUsing code I modified from the internet I have gotten my table filter the date correctly, however when the button is clicked it displays no results until I manually click ok on the filter VBA applied, once I do this it displays the correct results have I missed a trick here ?

Sub FilterListOrTableData()

    Dim ACell As Range
    Dim ActiveCellInTable As Boolean
    Dim FilterCriteria As String
    Dim Created As String
    Dim sToday As String
    Dim sStartDate As String
    Dim dStartDate As Date
    Dim sUpperBound, sLowerBound As String


    'Check to see if the worksheet is protected.
    If ActiveSheet.ProtectContents = True Then
        MsgBox "This macro will not work when the worksheet is write-protected.", _
               vbOKOnly, "Filter example"
        Exit Sub
    End If

    'Set a reference to the ActiveCell named ACell. You can always use
    'ACell now to point to this cell, no matter where you are in the workbook.
    Set ACell = ActiveCell

    'Test to see if ACell is in a table or list. Note that by using ACell.ListObject, you
    'don't need to know the name of the table to work with it.
    On Error Resume Next
    ActiveCellInTable = (ACell.ListObject.Name <> "")
    On Error GoTo 0

    'If the cell is in a list or table, run the code.
    If ActiveCellInTable = True Then
        'Show all data in the table or list.
        On Error Resume Next
        ActiveSheet.ShowAllData
        On Error GoTo 0

        'This example filters on the first column in the List/Table
        '(change the field if needed). In this case the Table starts
        'in A so Field:=1 is column A, field 2 = column B, ......
        'Use "<>" & filtercriteria if you want to exclude the criteria from the filter.
        'FilterCriteria = InputBox("What text do you want to filter on?", _
                           '       "Type in the filter item.")
      '  ACell.ListObject.Range.AutoFilter _
              '  Field:=1, _

             '  Criteria1:="=" & FilterCriteria
             'This example filters on the ActiveCell value.
'ACell.ListObject.Range.AutoFilter _
 '   Field:=ACell.Column - ACell.ListObject.Range.Cells(1).Column + 1, _
  '  Criteria1:="=" & ACell.Text

    sToday = Date
    dStartDate = DateValue(Date) - 7
    sStartDate = Str(dStartDate)

    sLowerBound = ">=" + sStartDate
    sUpperBound = "<=" + sToday

    ACell.ListObject.Range.AutoFilter Field:=89, Criteria1:=sLowerBound, _
    Operator:=xlOr, Criteria2:=sLowerBound

    ActiveWorkbook.RefreshAll

    'Else
       ' MsgBox "Select a cell in your list or table before you run the macro.", _
               'vbOKOnly, "Filter example"
    End If

End Sub

This is what the macro records when I do the manual option:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.ListObjects("FTLOG").Range.AutoFilter Field:=84, Criteria1:= _
        ">=29/11/2016", Operator:=xlOr, Criteria2:=">=29/11/2016"
End Sub
2
vbaexcelfilterrefresh
The filter through the code is generated with a basis of the activecell. E.g., click on the line which you want to filter and run it. – Vityata
Yeah for the button to work I have to select any cell in table before it will work I will change this to a set value later on, currently the code works but nothing is displayed until I click on the customer filter created by VBA, then when I click ok on that it displays all the correct results, this issue has me totally perplexed. – Calum
Can you give a few screenshots? Seems interesting – Vityata
Could only add 1 screenshot so added the one of me manually going into the customer filter VBA applied when I click ok there the table goes from being empty to showing the correct results – Calum
I am sorry, I still do not get it. So, with your code you get a filter, but the data is not filtered? Try removing the ActiveWorkbook.RefreshAll from the code. – Vityata

2 Answers

0
votes

The problem lies in the logic not the AutoFilter

enter image description here

xlOR will show all records that meet 1 of the conditions

Case 1: Show this record because Condition 2 is True

  • Condition 1: 1/1/2016>=2/1/2016 : False
  • Condition 2: 1/1/2016<=12/6/2016 : True

Case 2: Show this record because Condition 1 and Condition 2 is True

  • Condition 1: 7/31/2016>=2/1/2016 : True
  • Condition 2: 7/31/2016<=12/6/2016 : True

Case 3: Show this record because Condition 1 and Condition 2 is True

  • Condition 1: 7/31/2016>=2/1/2016 : True
  • Condition 2: 7/31/2016<=12/6/2016 : True

Case 3: Show this record because Condition 2 is True

  • Condition 1: 12/31/2016>=2/1/2016 : True
  • Condition 2: 12/1/2016<=12/6/2016 : True

xlAND will show all records that meet all of the conditions

Case 1: Hide this record because Condition 1 is False

  • Condition 1: 1/1/2016>=2/1/2016 : False
  • Condition 2: 1/1/2016<=12/6/2016 : True

Case 2: Show this record because Condition 1 and Condition 2 is True

  • Condition 1: 7/31/2016>=2/1/2016 : True
  • Condition 2: 7/31/2016<=12/6/2016 : True

Case 3: Hide this record because Condition 2 is False

  • Condition 1: 7/31/2016>=2/1/2016 : True
  • Condition 2: 7/31/2016<=12/6/2016 : True

Case 3: Show this record because Condition 2 is True

  • Condition 1: 12/31/2016>=2/1/2016 : True
  • Condition 2: 12/1/2016<=12/6/2016 : True
Sub FilterOr()
    Range("$A$5:$F$8").AutoFilter Field:=2, Criteria1:= _
        ">=" & Range("StartDate"), Operator:=xlOr, Criteria2:="<=" & Range("Today1")
End Sub

Sub FilterAnd()
    Range("$A$5:$F$8").AutoFilter Field:=2, Criteria1:= _
        ">=" & Range("StartDate"), Operator:=xlAnd, Criteria2:="<=" & Range("Today1")
End Sub
0
votes

enter image description here

This works:

Option Explicit

Public Sub TestMe()

    Dim dToday          As Date: dToday = Date
    Dim dStartDate      As Date: dStartDate = Date - 7
    Dim dEndDate        As Date: dEndDate = Date

    Dim sLowerBound     As String
    Dim sUpperBound     As String

    dToday = Format(dToday, "dd/mm/yyyy")
    dStartDate = Format(dStartDate, "dd/mm/yyyy")
    dEndDate = Format(dEndDate, "dd/mm/yyyy")

    sLowerBound = ">=" & CLng(dStartDate)
    sUpperBound = "<=" & CLng(dToday)

    ActiveSheet.Range("$A$1:$B$6").AutoFilter Field:=2, Criteria1:=sLowerBound, Operator:=xlOr, Criteria2:=sUpperBound

End Sub