CODEWITHSUNDEEP
excelvbaautofilter
jan benes
jan benes

Reputation: 33

Autofiltering by criteria1

After applying autofilter my data are not shown.

If I apply the same filter by hand, the data are shown correctly.

Private Sub FilteringReport()
    Workbooks.Open Filename:="C:\Users\benesjan\Desktop\Benes_jan\Excel_VBA\test_file\preventive_report.xlsx"
    Workbooks("preventive_report.xlsx").Sheets("Sheet1").Activate
    
    'Todays date, or custom date
    If Workbooks("PMM_reports.xlsm").Sheets("Sheet1").CheckBox1.Value = True Then
        edate = Worksheets("Sheet1").Range("B6").Value
    Else
        uedate = Now()
        edate = Format(uedate, "dd.mm.yyyy")
    End If
            
    MsgBox edate
    
    'edate is 10.08.2022
    With Sheets("Sheet1")
        .Range("A:J").AutoFilter Field:=5, Criteria1:=""
        .Range("A:J").AutoFilter Field:=7, Criteria1:="<" & edate
    End With 
  
End Sub

I am filtering this table
Excel table

The first filter works and returns
enter image description here

After I apply the second filter I get
enter image description here

If I manually click ok, it reapplies it and it refreshes itself and starts working. Refreshing the whole workbook or reapplying the filters does not work, even recorded macro does not work.

Upvotes: 0

Views: 86

Answers (1)

jan benes
jan benes

Reputation: 33

After a few changes, the code looks like this.

Sub FilteringReport()
        Workbooks.Open Filename:="C:\Users\benesjan\Desktop\Benes_jan\Excel_VBA\test_file\preventive_report.xlsx"
        Workbooks("preventive_report.xlsx").Sheets("Sheet1").Activate
                
                
        'Todays date, or custom date
        If Workbooks("PMM_reports.xlsm").Sheets("Sheet1").CheckBox1.Value = True Then
            edate = Workbooks("PMM_reports.xlsm").Sheets("Sheet1").Range("B6").Value
        Else
            edate = Date
        End If
                
        With Sheets("Sheet1")
            .UsedRange.AutoFilter Field:=5, Criteria1:=""
            .UsedRange.AutoFilter Field:=7, Criteria1:="<" & CDbl(edate), Operator:=xlOr, Criteria2:="=" & CDbl(edate)
        End With
 End Sub

Formating the Now() function caused the data to not filter correctly, by using Date function, I was able to make the filter work. Thanks Foxfire And Burns And Burns

Upvotes: 1

Related Questions