Pivot Table descending sort order of (Blanks)

[bezwlosy]

Hello,

I have a database from which I'm forming a pivot table. The problem is I have blanks as ID, so when I sort descending by the DataField, the (blanks) are on top. I would love them to appear under every other data, or it would be perfect if they would be just sorted corectly, so the blanks would find themselves between he other IDs.

I think the picture will explain everything. I want (blanks) to be sorted correctly among other data, or if not possible, to show under the filled-in IDs.

I'm using a VBA code to sort:

With .PivotFields("ID")
.AutoSort Order:=xlDescending, Field:="Value"
End With

Is there any way to help me or achieve to desired effect in different way?

Thank everyone for help.

 

[par60056]

are you saying that if the ID is blank, it should have the ID of the row above? If you are putting the data into a pivot, then you should fill in those blanks with the right values before making the pivot.

 

[bezwlosy]

are you saying that if the ID is blank, it should have the ID of the row above? If you are putting the data into a pivot, then you should fill in those blanks with the right values before making the pivot.

No, unfortunately it should have another ID, but it is not known. It would be ok if the blanks appear on the bottom,not on the top.
Thank you.

 

[par60056]

The only choice then is to do it with a manual sort.

The sad thing is that if you autosort and then manually change 1 it reverts back to the unsorted before moving the sort value.

I have a couple places I do something similar. Do the automatic sort, read the pivotfield values into an array (now they are in the right order) then loop over the array setting the position for each and then set the position for "(Blanks)" to be last.

If you have the pivot table set for manual update it is not too slow.

 

[bezwlosy]

I also posted here: Pivot Table descending sort order of (Blanks) + VBA

Manual sort it is just to drag it?

The way you are doing seems quite complicated for me. Sometimes I can have in my pivot more then 10k rows, will it still work quickly?
Thanks.

 

[par60056]

I can't see the code on the link you posted. Don't have access there.

No you don't have to drag it.

I took your test table and wrote this little sorter.

See if it helps you.

Sub sortit()
Dim idVals(1000) As String
Dim itemNo As Integer


Application.ScreenUpdating = False
With ActiveSheet.PivotTables("PivotTable1")
    .ManualUpdate = True
    With .PivotFields("ID")
        .AutoSort Order:=xlDescending, Field:="Value"
    End With
    .ManualUpdate = False
    .ManualUpdate = True
    With .PivotFields("ID")
        For itemNo = 1 To .PivotItems.Count
            idVals(itemNo) = .PivotItems(itemNo)
        Next
        For itemNo = 1 To .PivotItems.Count
            .PivotItems(idVals(itemNo)).Position = itemNo
        Next
        .PivotItems("(blank)").Position = .PivotItems.Count
    End With
    .ManualUpdate = False
End With
Application.ScreenUpdating = True
End Sub

 

[par60056]

hmmm there seems to be a problem since it is sorting in another field. It isn't getting the IDs in the right order. Trying to fix it.

 

[par60056]

ok this is a little more brute force. It seems when you autosort by another column it doesn't actually change the order of the IDs in the pivot field.

But this should still be pretty fast even with 10K rows in the pivot.

Sub sortit()
Dim idVals(1000) As String
Dim itemNo As Integer
Dim currRow As Long


Application.ScreenUpdating = False


With ActiveSheet.PivotTables("PivotTable1")
    .ManualUpdate = True
    With .PivotFields("ID")
        .AutoSort Order:=xlDescending, Field:="sum of Value"
    End With
    .ManualUpdate = False
    .ManualUpdate = True
    itemNo = 1
    For currRow = .TableRange1.Row + 2 To .TableRange1.Row + .TableRange1.Rows.Count - 1
        If (Cells(currRow, .TableRange1.Column) <> "" And InStr(1, Cells(currRow, .TableRange1.Column), "total", vbTextCompare) = 0) Then
            idVals(itemNo) = Cells(currRow, .TableRange1.Column)
            itemNo = itemNo + 1
        End If
    Next
    With .PivotFields("ID")
        For itemNo = 1 To .PivotItems.Count
            .PivotItems(idVals(itemNo)).Position = itemNo
        Next
        .PivotItems("(blank)").Position = .PivotItems.Count
    End With
    .ManualUpdate = False
End With
Application.ScreenUpdating = True
End Sub

 

[bezwlosy]

Hey par60056, thank you for your effort.

I have to postpone this task, but I will check your code in some days, thank you so much. The problem might be that there can be more PivotFields on the left of ID?

 

[par60056]

I wrote the code based off the sample pivot you did. I know that was not the real table. You will need to make some modifications to make it fit your table. But it does work with the table in the sample.

Hope it helps you out. If there are issues, follow up on this thread since I may not see a new one.