Results 1 to 10 of 10

Thread: Reports and auto-adjusting of row height

  1. #1

    Reports and auto-adjusting of row height



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a document with a series of data tables that are 13 columns wide; they have numbers, dates and percentages. Each employee has his/her own sheet with his/her own data.

    I currently have a canned report within the same document that can pull data from each employee’s own sheet to produce a personalized report. For some of the data types, the report provides a summary for the four weeks (current week plus three previous weeks). Making the various formulae work is not a problem, even though some of the current formulae look more like spaghetti code of old.

    I’ve been asked to reconfigure the report to make it more flexible, to enable a more targeted report for different employees who might not have the same data types in their particular reports (TMI?). I’ve figured out how to make a new report get and apply information in a nice variety of ways using drop-down lists. That’s going to be a great help; here is the “but” part of the request.

    Is there a way to make the row height dynamic—self-sizing according to content?

    As an example, I have one item that has a drop-down list with five items in it. Three of the items will occupy only one normal row; no problems there. One of the items is long enough that it will wrap and become two rows, and one is long enough that it will wrap and take up four rows. I know how to manually resize the rows, but if I can “teach” the sheet to resize itself in accordance with the cell’s content, I will have a lot of happy report creators.

    Second thing on my wish list is to get the report to reset itself back to the basic set-up when I change employees. I would love to have an eloquent solution for this so that the end-users will not have to fight with large gaps and row resizing for each report to be generated.

    Each report is run separately because we need to be able to input at least one customized paragraph to address specific plus or minus situations for each individual based on their work paths, as well as positive or negative results that need to be addressed.

    Thanks in advance.
    --
    DeanZF

  2. #2
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    If you can accept VBA, then the following snippet will do want you want.
    Change the Row numbers to suit your selection

    Sub RowAutofit()

    Rows("1:20").EntireRow.AutoFit


    End Sub
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  3. #3
    Quote Originally Posted by Roger Govier View Post
    If you can accept VBA, then the following snippet will do want you want. Change the Row numbers to suit your selection

    Sub RowAutofit()

    Rows("1:20").EntireRow.AutoFit

    End Sub
    Thanks, Roger. Currently I don't think there is a VBA component. I'll investigate that.

    If there is NOT a VBA component, I'll need some coaching on how to implement it.
    --
    DeanZF

  4. #4
    Well, Roger, with a little bit of research, I know beyond a shadow that there is indeed a VBA component to the existing workbook/worksheets. As soon as tomorrow, I'll be able to play with it and hopefully get it incorporated into the existing code. I am going to have a bit of a learning curve ahead of me, but I am now armed with a book on Excel's VBA and am off to the races.

    Again, thanks for your quick response.

  5. #5
    Okay, me again. Yes, there is definitely a VBA component. I've tried to insert the code and cannot get it to reduce the row height. Code with the insertion looks like this:

    Private Sub CommandButton1_Click()

    Sub RowAutofit()

    Rows("22:30").EntireRow.AutoFit

    Sheets("Memo").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Input").Select
    Range("E6").Select

    End Sub

    First time, I left the "end sub" that was provided with the other two lines. It aborted because of an unexpected end sub.

    Thanks in advance for your eyes and wisdom.

  6. #6
    good idea

  7. #7
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi

    Sorry, but I haven't logged in for a while, so only saw your message today.

    Because you already have a macro linked to you button, you don't need the complete module I sent you, just the one line

    Private Sub CommandButton1_Click()


    Rows("22:30").EntireRow.AutoFit

    Sheets("Memo").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Input").Select
    Range("E6").Select

    End Sub
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  8. #8
    The autofit command does not work the way I would have hoped. I think it's because all of the rows involved have their content in columns C, D, & E that the command looks at the default font size for column A (all empty) and changes it to fit only one character in that empty cell.

    Can I get the autofit command to look at column E to use as a basis for its sizing? Also, can I get it to do an "autosize plus 8" somehow? All of those cells have text in them and need the extra 8 to give a bit of grace to the various paragraphs without the need for a spacer/empty row between data-bearing rows.

    Thanks Roger.

  9. #9
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi
    I am now getting confused
    Are you now talking about Column Width, as opposed to Row Height?

    If a row has nothing in it, at all, then it will remain as a standard height row.
    If there is a larger font used in say column D, with noting else on that row, the autofit will make the row height fit the font size in column D.

    You an also autofit Columns, you would just nee to include the line
    Columns("C:G").EntireColumn.AutoFit
    adjusting the column letters to what you require.
    If there is nothing in the column, then it will leave it at its previous width, which will be the default width, or whatever you may have widened it to.

    If this doesn't solve your problem, try uploading your file with a more detailed explanation of what you are trying to achieve.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  10. #10
    no, sorry, not talking about column width. The autofit row height seems to take its cue from what's in the first cell in the row. because of the shape of this report the contents that require attention are not in that first cell in the row. Data defining the needs are in the fifth cell in the row. I will need to sanitize the file before I can upload it, but if that's an option, I'll do that! Thanks, Roger.

    The newest problem that I've encountered with this crazy project deals with the differences between screen fonts and printer fonts. It's making another mess to deal with. Sigh

Posting Permissions

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