Pivot Table VBA Filter for dates before

Question

I am new to this forum, and would really appreciate some guidance on an issue I've been dealing with for the past 4 days.

I have a macro to filter a pivot table for dates before a date on cell B5 in the same sheet. Unfortunately, it yields some data, but not all the data.

Below is the code I have (based on research/copy paste), I apologize if the code is not best practice. I have also attached a picture for reference as well. Thank you in advance for your review and support.

'Sub BeforeDate()

Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem

    'Set the variables
    sSheetName = "PivotTable"
    sPivotName = "PivotTable1"
    sFieldName = "apptdate"
    'sFilterCrit = "5/2/2019"
    sFilterCrit = ThisWorkbook.Worksheets(sSheetName).Range("B5").Value

    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
        'Clear all filter of the pivotfield
        .ClearAllFilters

        'Loop through pivot items of the pivot field
        'Hide or filter out items that do not match the criteria

        For Each pi In .PivotItems
            If pi > Range("B5") Then
                pi.Visible = False
            Else
                pi.Visible = True
            End If
        Next pi

    End With

End Sub

Baledin · Accepted Answer · 2020-02-25 23:39:49Z

1

A couple of items wrong with your code. First, to convert a string to a date, wrap it in CDate() (i.e CDate(ThisWorkbook.Worksheets(sSheetName).Range("B5").Value)).

Further, your For Each is comparing pi (the PivotItem object) to Range("B5") - I believe that you meant to refer to sFilterCrit instead of Range("B5"), and pi should be pi.Value (I think it will still work as in in VBA but you really want the Value, not the object). Additionally, this should be passed to CDate() just as the sFilterCrit value was above.

Finally, you can simplify your If statement by using the following: pi.Visible = CDate(pi.Value) < sFilterCrit Essentially, the result of your If statement (true or false) is passed to the Visible property (note the switched comparison operator).

Here is the updated code:

Sub BeforeDate()

Dim sSheetName As String
Dim sPivotName As String
Dim sFieldName As String
Dim sFilterCrit As String
Dim pi As PivotItem

    'Set the variables
    sSheetName = "PivotTable"
    sPivotName = "PivotTable1"
    sFieldName = "apptdate"
    'sFilterCrit = "5/2/2019"
    sFilterCrit = CDate(ThisWorkbook.Worksheets(sSheetName).Range("B5").Value)

    With ThisWorkbook.Worksheets(sSheetName).PivotTables(sPivotName).PivotFields(sFieldName)
        'Clear all filter of the pivotfield
        .ClearAllFilters

        'Loop through pivot items of the pivot field
        'Hide or filter out items that do not match the criteria

        For Each pi In .PivotItems
            pi.Visible = CDate(pi.Value) < sFilterCrit
        Next pi

    End With

End Sub

answered Feb 25, 2020 at 23:39

Baledin

434 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Baledin Over a year ago

I meant to add that the core problem was that VBA was comparing a string to a string, not a date to a date. String comparisons work a bit differently than you may expect (for example 01/25/2020 gets sorted before 1/24/2020).

MikeACF Over a year ago

Thank you so much! It worked! I really appreciate you taking the time to review this and provide guidance/the solution. You have no idea how much I really appreciate your help!

Baledin Over a year ago

I've been there often enough myself. Good luck!

Collectives™ on Stack Overflow

Pivot Table VBA Filter for dates before

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related