Alphabetize values in pivottable drop down in Excel using VBA

Question

If you have a pivot table set up and you click the drop down for a field, and looking at the value filter section that shows up, I would expect and want this to show up in alphabetical order. For example, if the field is states, I have pivots where it shows up Missouri, Oklahoma, ... and it makes it hard to find what I'm looking for. Or, imagine a date field where the dates are completely random when you try to filter.

I have googled for how to get this list alphabetized and found mostly solutions that are not ideal, i.e., there may not exist any nice way to do this built in to Excel. I am looking for solutions, especially those involving VBA, because I have many pivot tables, some with many different fields, that I want to fix. Doing it manually is only an option for a few, if that is the only option.

Just to be clear, I am not talking about sorting the pivot table itself. I know how to do that. I am talking about sorting the values in the value filter portion of the drop down so that I can easily filter my pivot table down to the specific categories I care about.

I am using Excel 2007.

it would help to add a sample screenshot to your question. Also, some code to show what you have tried. Have you thought of iterating through the drop downs within filtered range (before applying the filter) and choosing the values using VBA? What would the conditions for filtering be? — user2140173
– user2140173, Commented Jun 11, 2013 at 15:34

GeoffDS · Accepted Answer · 2013-06-11 19:41:23Z

1

It turns out that when a field is in the filters, you can't do anything with it. But, when the item is in the rows, you can just sort it. So, one solution is to move something from the filters to the rows, then sort, and then move it back to the filters. This is okay if you have a few of these to do. However, even with that, if you have any other cells nearby that have data that would be overridden once the pivot table changes size, then doing this manually is extremely difficult.

But, with VBA, you can sort fields even when they're in the fields section of the pivot table!

For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables

        pt.ManualUpdate = True

        For Each pf In pt.PivotFields
            pf.AutoSort xlAscending, pf.Name
        Next pf

        pt.ManualUpdate = False

    Next pt

Next ws

answered Jun 11, 2013 at 19:41

GeoffDS

1,2815 gold badges19 silver badges32 bronze badges

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

Comments

Mr Eyesopen · Accepted Answer · 2021-01-27 15:23:02Z

Bit late for OP but as this was useful to me.. Following on from GeoffDS, If you only want to sort the fields in the 'Filters' section of the pivot table, you can add an if statement to restrict the sort to fields that have a pf.orientation of 'xlPageField'. It's also safer to sort using SourceName rather than Name, as the Sourcename is fixed, but a user can change the Name causing the routine to crash.

Other pf.Orientations are xlRowField, xlColumnField, xlDataField, for used fields and xlHidden for unused fields.

For Each ws In ThisWorkbook.Worksheets
    For Each pt In ws.PivotTables

        pt.ManualUpdate = True

        For Each pf In pt.PivotFields
           If pf.Orientation = xlPageField then

              pf.AutoSort xlAscending, pf.SourceName

           End If
        Next pf

        pt.ManualUpdate = False

    Next pt

Next ws

Collectives™ on Stack Overflow

Alphabetize values in pivottable drop down in Excel using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related