none
Hide/Move/Delete "Row Labels" dropdown list RRS feed

  • Question

  • I apologize if this solution is out there but I'm unable to find it. I've created a pivot table using VBA and I'm trying to figure out how to stop the "Row Labels" (IN PICTURE) text on the row labels dropdown box from covering up my first field heading... the "Row Labels" is in the cell where my header "Segment" should be and I just simply can't figure out how to get rid of it! I need to have the pivot table in this tabular format because the "Classic PivotTable Layout" will not let me get the presentation required. The "Display field captions and filter drop downs" removes the "Row Labels" text but it also gets rid of all the headers. How do I move, hide, or get rid of the filter drop downs?

    Row Labels is covering "Segment" and I'm not able to get rid of it so I can get at the "Segment" title in my code. ANY SOLUTION WOULD BE GREATLY APPRECIATED as well as any explanation as to why it's just not possible if it's not.


    Michael Oskierko VBNOOB

    Tuesday, February 27, 2018 5:58 PM

All replies

  • The only thing I've figured out is if I change the pivot table from it's tabular format to the classic format and back again it will display correctly. HOWEVER, when I perform this in CODE it has ZERO EFFECT!

    Michael Oskierko VBNOOB

    Tuesday, February 27, 2018 5:59 PM
  • Hello Michael Oskierko,

    >>HOWEVER, when I perform this in CODE it has ZERO EFFECT!

    What's your code, in my test, it could be done in code. Here is my code for testing.

    Dim PIT As PivotTable
    Set PIT = ActiveSheet.PivotTables(1)
    'set pivot table to classic format
    With PIT
    .InGridDropZones = True
    .RowAxisLayout xlTabularRow
    End
    'back to  tabular format
    PIT.InGridDropZones = False

    >>Hide/Move/Delete "Row Labels" dropdown list 

    Do you still need hide the dropdown list for filtering?

    If so, please refer to below code.

    For Each pf In PIT.RowFields
    pf.EnableItemSelection = False
    Next pf

    Here is the demonstration.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 3:09 AM
  • I appreciate the response! I'm not sure we are starting from the same origination point though. I notice the "Sum of Pricing" is in cell A1 and the field rows (titles) start on cell A3. I'm starting with a tabular pivot table and as such, in your example, the "Sum of Pricing" would be automatically placed over cell A2.

    From that point if I manually convert from tabular to classic pivot and then back to tabular the label in A2 disappears. When I run the enableitemselection = false on all fields it works and the .InGridDropZones on all fields in code it shows me the original tabular table with the "Sum of Pricing" covering segment. It's either because we've started at different origination points or it's some obscure setting on my pc.


    Michael Oskierko VBNOOB

    Wednesday, February 28, 2018 8:38 PM
  • Hello Michael,

    I would suggest you share a simply excel file so we could use it to reproduce your issue,

    For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 1, 2018 6:39 AM