# Height conflict

#### elisep

##### New Member
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.

thanks much!
e

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

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

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

Code:
``````Sub autofit()

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

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

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

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

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

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

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

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

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.

Code:
``````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

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

Replies
3
Views
463
Replies
4
Views
400
Replies
2
Views
4K
Replies
3
Views
1K
Replies
22
Views
2K

1,196,208
Messages
6,014,019
Members
441,802
Latest member
Aneurysm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back