+ Reply to Thread
Results 1 to 6 of 6

How to fix the row height of a pivot table

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Switzerland
    MS-Off Ver
    MS Excel 365
    Posts
    68

    How to fix the row height of a pivot table

    Hi!

    Is there any way/trick in order to keep the row height of a pivot table (Excel 2007), once the table has been refreshed?

    I have...

    - unchecked the "Autofit column widths on update" option
    - checked the "Preserve cell formatting on update" option
    - tried the "wrap text" trick

    But all without luck!

    Any idea?

    Thanks,
    FixandFoxi

  2. #2
    Registered User
    Join Date
    09-20-2013
    Location
    LV, NV
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: How to fix the row height of a pivot table

    I haven't found any settings that would retain a row height after a data refresh. (XL07 on XP)

    You could try some VBA to adjust the row heights after you update. Do this in two parts; 1-Create a range name that will always automatically adjust to the columns and rows, 2-Use the Range Name to set the range in a macro that adjusts the row height.

    Part One
    Use Name Manager to add New range
    in the Refers to: box paste this formula
    =OFFSET(ThisWeek!$B$7,1,0,COUNTA(ThisWeek!$A:$A)-4,COUNTA(ThisWeek!$7:$7)-2 )

    In my Pivot Table the field titles are on row 7 on a sheet named ThiWeek. It starts in column A and the first data are at B7

    Part Two
    Add a new module to your workbook (or paste into an existing one). Change row height from 15.5 to suit your needs.

    Sub AdjustNamedRangeRowHeight()
    '
    ' Macro uses a named range to select rows then adjusts row height
    '
    '
    Range("ThisWeekPTData").Select
    Selection.RowHeight = 15.5
    End Sub

  3. #3
    Registered User
    Join Date
    03-30-2014
    Location
    Qatar
    MS-Off Ver
    Excel 2013
    Posts
    1

    Re: How to fix the row height of a pivot table

    The simplest way I could manage this was click on ANY COLUMN OUTSIDE the Pivot Table range (which will be the normal excel sheet area).
    Highlight the entire column, Select Format Cell, Select the FONT SIZE which will be close to your Row Height.
    Get back to the pivot table. The Pivot Table row height changes accordingly and remain the same when you filter the report. If you want to increase or decrease the row height, repeat the exercise outside the pivot table by in increasing or decreasing the font size.
    This may not be the best/proper solution, but it works.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to fix the row height of a pivot table

    vimalkar welcome to the forum

    Thanks for the input on this
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    05-02-2016
    Location
    vimercate
    MS-Off Ver
    2010
    Posts
    1

    Re: How to fix the row height of a pivot table

    vilkmar! great solution! thanks!!!

  6. #6
    Registered User
    Join Date
    03-01-2021
    Location
    Dhaka, Bangladesh
    MS-Off Ver
    2019
    Posts
    4

    Thumbs up Re: How to fix the row height of a pivot table

    Dear vimalkar, Thanks for your help!!
    It really works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a macro that locks row height and wrap text in a pivot table
    By jaimeteele in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-29-2013, 10:53 AM
  2. Row Height Changes When Pivot Table is Refreshed
    By PosseJohn in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-29-2012, 06:21 PM
  3. Collapse/Expand - Pivot table Fields - Need equivalent option in Excel VBA Pivot table
    By ragavendraph in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-07-2012, 03:00 PM
  4. Replies: 1
    Last Post: 06-20-2010, 04:00 AM
  5. Return pivot table range...not the data table, the PIVOT TABLE!
    By Air_Cooled_Nut in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-26-2008, 01:07 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1