Height conflict

[elisep]

Hi!

I've got an excel file (should really be a database, but that's another issue!) that's got lots of needs. Among them, my boss would like to set a minimum row-height. We have some very long (text) cell-contents, though, and I'm finding that when I specify row height there's no way to auto-fit to show all of the longer cells. Is there a workaround?! There's no way that I could go through and manually autofit cells every time they snap back to a specified height (which seems to happen every time I run a 'sort' on the file.

HELP! (please!!)

thanks much!
e

 

[Andywiz]

Hi Elisep

Please clarify - do you want to be able to easily specify the height of every row and have that height stay the same regardless of cell content, or do you want every row to autofit to the contents of the cells within it whenever changes are made?

And do you wish to do this on column widths as well, or just row heights?

Some more info will help to give you the result you need.

Andywiz

 

[elisep]

that's just the problem... i need both, a combination of the two. i need to have a certain amount of "cellpadding" as it were, so that rows that would autofit to a single row are taller (36 is where we'd like to set it -- is that 36 points?), but rows that have data cut off will expand so that all the data is viewable. so, a minimum height of 36, max determined by cell contents. and columns aren't a problem.

thanks!
e

 

[Andywiz]

OK, you could accomplish this with a Macro, as follows:

Sub autofit()

Range("A:A").Rows.autofit

fstrow = 2
lstrow = Range("A65536").End(xlUp).Row
pad = 5

For n = fstrow To lstrow
    Cells(n, 1).Rows.RowHeight = Cells(n, 1).Rows.RowHeight + pad
    Next n

End Sub

Explanation:
1. Firstly autofits all rows on the sheet; then
2. Sets a starting row as 2 (allowing for any headers on row 1), and last row as the lowest row with an entry in column A; then
3. Loops through all the rows and adds a padding to the height (set here as 5 pts but can be changed to whatever is required).

I've tried it on a sample sheet and it seems to perform what you want. Do you want help implementing or customising the Macro?

Andywiz

 

[elisep]

oh that is wonderful!!!

It seems to be working fine for me too. One question, in terms of customizing. Would there be a way for it to overlook cells that are already propped open by their contents? So that cells that are already taller than a specified height could be overlooked?

Thank you thank you thank you!

elise

 

[elisep]

And you know, then again, this is so perfect, that I don't think I need that last detail. This way they can write comments/updates even in those over-populated (!) cells. Perfect.

Thank you again, Andywiz.

elise

 

[elisep]

Edit: I figured this out... there's a learning curve here! Thanks again for your help.

Oh, wait!

I do have a question. I initially saved this to one of the worksheets in the workbook. Where can I save it within the workbook to run on every sheet? (Simple question, I'm sure, but!)

Thanks much!

e

 

[Andywiz]

Elise - sorry I've been away and you appear to have had a conversation with yourself!

I'm glad its working for you.

With regard to your last question, it should work with all the worksheets in a single workbook, provided you have saved the code as a sub in a module. i.e. you should be able to call the macro, regardless of whichever sheet you are in.

Or do you mean that when you run the code, you want it to affect ALL sheets at once?

Andy

 

[elisep]

Well, I figured out how to save it to the 'ThisWorkbook' module and that way it seems that I can call it from any sheet in the document.

And then I realized that it would be fabulous if I could run it once and have it go through every worksheet in the workbook, since this height thing applies to all sheets. I've been fiddling with the code for that for the last hour, and doing pretty dismally (I'm trying to piece apart code from someone else's request to apply very different code to every sheet in her workbook...)

I'm sure this isn't all that hard, if it weren't for my vague understanding (learning as I read the code, and then trying different possible solutions -- the dart board method).

Would you help, again?

elise

 

[Andywiz]

No problem,

This probably isn't the best way to do it, but I've just nested the original code within another, which now loops through all the sheets in the activeworkbook to perform the same function.

I'm sure that a real expert would be able to write this in a simpler way - but I have tested it again and it works on my sample.

Sub autofit()

Dim pad As Integer, fstrow As Integer, lstrow As Integer
Dim sht As Worksheet

For Each sht In ActiveWorkbook.Sheets
    
sht.Range("A:A").Rows.autofit

fstrow = 2
lstrow = sht.Range("A65536").End(xlUp).Row
pad = 5
    
For n = fstrow To lstrow
    sht.Cells(n, 1).Rows.RowHeight = sht.Cells(n, 1).Rows.RowHeight + pad
Next n

Next sht

End Sub

The only other addition here is the declaration of the variable types at the top of the macro - the Dim statement. This is apparently best practice when writing code with variables in it (i.e. substituting a fixed value with an alias).

Hope this works for you too - happy to help out more if you need it.

Cheers,
Andy